Formatting Issue

[mrsushi]

The below code formats any row column B to N which has a value greater than 30 (in column O)

Sub FormatRangeEQUITIES()

Range("$B$5:$O$500").Select
With Range("$B$5:$O$500")
.FormatConditions.DELETE
.FormatConditions.Add Type:=xlExpression, Formula1:="=$O5 >= 30"
.FormatConditions(1).Interior.ColorIndex = 37
End With

End Sub

What I need now is to highlight the row if column O equal to "CHECK". The below code doesn't seem to work. Any ideas? Also, how would the code be tweaked to use the last row method instead of specifying a range?

Sub FormatCHeck()
Range("$B$5:$O$500").Select
With Range("$B$5:$O$500")
.FormatConditions.Add Type:=xlExpression, Formula1:="=$O5 =""CHECK"""
.FormatConditions(1).Interior.ColorIndex = 37
End With

End Sub

 

[Dan_W]

I think the issue maybe down to if columns P or O are showing "#Value!". If this is the case, the row wont highlight despite Column R or S showing "CHECK"

The formula in column O = IF(ABS(ABS(R8)-(ABS(K8))>2),"CHECK",IF(ABS(ABS(R8)-(ABS(K8))<-2),"CHECK","OK"))
The formula in Column P = IF(ABS(ABS(S8)-(ABS(M8))>2),"CHECK",IF(ABS(ABS(S8)-(ABS(M8))<-2),"CHECK","OK"))

The formula is calculating if the difference is less than -2 or greater that 2, between O to R and comparing P to S. If Column R and S are flagging up "CHECK", this causes column P and O to show the error "#Value!". I think I need an amendment of the formula above to produce a "CHECK" instead of the "#Value"!". Should the ISERROR function be incorporated? Many thanks

​

If Columns R and S flag "CHECK", it makes sense that this would cause column O at least to show the error "#Value!" - the formulas above are comparing absolute values of cells, which Excel clearly can't do if Column R goes from being a number to the word "CHECK" every so often. Are you cell references correct? Should the formula in Column O be referencing R8?

I suspect that your answer to both questions is 'yes', given that you're asking whether ISERROR should be incorporated. I think that using ISERROR is useful for debugging where something may have gone wrong in the formula's logic or the conditional formatting logic, but I think there is a tendency for people to rely on it too much 'just-to-get-the-thing-to-work'. But try it and see and let us know how it goes.

That said, I think that there is something odd going on with your spreadsheet. Jason's point re: "could be stray spaces or zero width characters" is a good one - does your computer have any other language keyboards installed? Namely, Asian-language keyboards? I ask because inexplicable issues I've had with formulas tend to occur when I'm using Windows in Japanese or if the spreadsheet is in Japanese. Some foreign languages (like Japanese), have half-width and full-width characters. It's a long boring story, but I think the sample below probably illustrates my point. The values in column A are the same as the corresponding values in column C, but A2 is written in 'full width' characters whereas the values in A1, C1, and C2 are in half-width. The same again for A4 v C4 and A5 v C5. Here, the alphabet charcters are all written in half-width, but with A5, I made the spaces 'full-width'.

So although the values in Column A are the same as Column C are, in reality, the same - Excel properly concludes that they are different in rows 3 and 5. But anyway, without looking at your formulas, that's the best alternative explanation I can come up with.

Book3
	A	B	C
1	CHECK	TRUE	CHECK
2	ＣＨＥＣＫ	FALSE	CHECK
3
4	This is a test	TRUE	This is a test
5	This　is　a　test	FALSE	This is a test
Sheet4

Cell Formulas
Range	Formula
B1:B2,B4:B5	B1	=A1=C1

 

[jasonb75]

I think the issue maybe down to if columns P or O are showing "#Value!". If this is the case, the row wont highlight despite Column R or S showing "CHECK"

The formula used as a conditional formatting rule must reutrn TRUE or a numeric value other than zero for the formatting to be applied, anything else will leave the original cell formatting in place. The #VALUE! errors in the cell formulas are being carried forward to the conditional formatting formula, so even though some cells contain "CHECK" and evaluate to TRUE, the #VALUE! error is over-riding them.

The 'correct' way to fix the issue would be to enclose the existing cell formula in IFERROR so that it shows "CHECK" instead of an error.

=IFERROR(IF(ABS(ABS(R8)-ABS(K8))>2,"CHECK","OK"),"CHECK")

It could also be done by error trapping the conditional formatting formula, but in my opinion that would be bad practice.

 

[mrsushi]

The formula used as a conditional formatting rule must reutrn TRUE or a numeric value other than zero for the formatting to be applied, anything else will leave the original cell formatting in place. The #VALUE! errors in the cell formulas are being carried forward to the conditional formatting formula, so even though some cells contain "CHECK" and evaluate to TRUE, the #VALUE! error is over-riding them.

The 'correct' way to fix the issue would be to enclose the existing cell formula in IFERROR so that it shows "CHECK" instead of an error.

=IFERROR(IF(ABS(ABS(R8)-ABS(K8))>2,"CHECK","OK"),"CHECK")

It could also be done by error trapping the conditional formatting formula, but in my opinion that would be bad practice.

Thanks Jason, that's done the trick.

The above applies to one part ie >2, but I also need this formula to factor in <2. I've tried with the below, but excel doesn't like this. Missing a parenthesis? Or is there another way to write this?

=IFERROR(IF(ABS(ABS(S87)-(ABS(M87))<-2),"CHECK","OK"),(IF(ABS(ABS(S87)-(ABS(M87))<-2),"CHECK","OK")"CHECK")

 

[jasonb75]

The formula in post 22 does both together.

If the result of any calculation is <-2 then the absolute value of that calculation will be >2 so the second check is pointless.

 

[mrsushi]

The formula in post 22 does both together.

If the result of any calculation is <-2 then the absolute value of that calculation will be >2 so the second check is pointless.

Many thanks for the above. I did test the values as follows

100 (column K) and 97 (column R ) = -3 which column O produced a "CHECK" (R minus K)
100(column K) and 103 (column R) = 3 which column O produced a "OK"(R minus K)

Given the formula, the latter should produce a "Check"? Looking at the formula, the absolute values only apply to the K and R. If the values changes, the difference will produce a negative or positive value. For some reason the ABS outside isn't producing an absolute value of +3 (in both cases)

Out of interest, how would the below be amended to incorporate the >2?

IFERROR(IF(ABS(ABS(R102)-(ABS(K102))<-2),"CHECK","OK"),"CHECK")

 

[jasonb75]

100(column K) and 103 (column R) = 3 which column O produced a "OK"(R minus K)

Unless you have changed the formula, then that appears to be a data error, not a formula error. Using the formula exactly as it was in my post, I get the correct "CHECK" result.

Book1
	K				O			R
8	100				CHECK			103
Sheet2

Cell Formulas
Range	Formula
O8	O8	=IFERROR(IF(ABS(ABS(R8)-ABS(K8))>2,"CHECK","OK"),"CHECK")

 

[mrsushi]

Unless you have changed the formula, then that appears to be a data error, not a formula error. Using the formula exactly as it was in my post, I get the correct "CHECK" result.

Book1
	K				O			R
8	100				CHECK			103
Sheet2

Cell Formulas
Range	Formula
O8	O8	=IFERROR(IF(ABS(ABS(R8)-ABS(K8))>2,"CHECK","OK"),"CHECK")

When using 103, it flags "check", if using 97, it flags "OK" (but the difference is -3). Based on the formula above the formula is >2. Can you try plugging in 97 your end to see if you get the same result?

 

[jasonb75]

I've tried it with 97 in K, 100 in R and with 100 in K, 97 in R. Both return CHECK.

Are you using the exact formula from my post, or one of your edited versions? There are considerable differences between my formula and your version in post 25!
Your version will return OK instead of CHECK because you have changed >2 to <-2 and moved some of the brackets to the wrong places.

 

[mrsushi]

I've tried it with 97 in K, 100 in R and with 100 in K, 97 in R. Both return CHECK.

Are you using the exact formula from my post, or one of your edited versions? There are considerable differences between my formula and your version in post 25!
Your version will return OK instead of CHECK because you have changed >2 to <-2 and moved some of the brackets to the wrong places.

Hi Jason, yes, looks like I may have changed the formula including an extra bracket.

Its worked now using =IFERROR(IF(ABS(ABS(R107)-ABS(K107))>2,"CHECK","OK"),"CHECK")

Apologies for the confusion.

Many thanks for your help on this (and patience)