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

 

[mrsushi]

Well, what would you want the conditional formatting to do?
Would it activate if "CHECK" was in any of those columns? (note that CHECK is case sensitive - writing "Check" for example, wouldn't trigger the conditional formatting)
Also, what is getting highlighted? The relevant row from column B to column O? Or from column B to column S?

Hi Dan,
The previous code did what was asked ie anything says "Check" in Column O highlights the row B to O. I should have indicated I actually wanted to highlight B to S (which I could tweak your code). The other columns P, R and S will also have "Check" too.

Basically, if there are "Check" in columns O, P, R and S, highlight the row B to S
Many thanks

 

[jasonb75]

Basically, if there are "Check" in columns O, P, R and S, highlight the row B to S

Try changing the formula in the code to

"=AND($O5:$S5=""CHECK"")"

 

[mrsushi]

Try changing the formula in the code to

"=AND($O5:$S5=""CHECK"")"

I tested this...doesn't seem to work when you have CHECK in column R and S

 

[jasonb75]

So want to look for "CHECK" in columns O, P, R or S, not O, P, R and S.

"=OR($O5:$S5=""CHECK"")"

 

[mrsushi]

So want to look for "CHECK" in columns O, P, R or S, not O, P, R and S.

"=OR($O5:$S5=""CHECK"")"

Thanks for the update.
However, I tested this and placed checks in the other columns. Didn’t highlight the rows.

? I may use the conditional formatting route, but will try and figure out the above

 

[jasonb75]

Try it this way instead

    .FormatConditions.Add Type:=xlExpression, Formula1:="=OR($O5=""CHECK"",$P5=""CHECK"",$R5=""CHECK"",$S5=""CHECK"")"

The method that I suggested earlier does work, but for some reason it doesn't work correctly when you use vba to add it to the sheet.

 

[mrsushi]

Try it this way instead

    .FormatConditions.Add Type:=xlExpression, Formula1:="=OR($O5=""CHECK"",$P5=""CHECK"",$R5=""CHECK"",$S5=""CHECK"")"

The method that I suggested earlier does work, but for some reason it doesn't work correctly when you use vba to add it to the sheet.

Thanks Jason, I'll give that a try and let you know the results.

 

[mrsushi]

Try it this way instead

    .FormatConditions.Add Type:=xlExpression, Formula1:="=OR($O5=""CHECK"",$P5=""CHECK"",$R5=""CHECK"",$S5=""CHECK"")"

The method that I suggested earlier does work, but for some reason it doesn't work correctly when you use vba to add it to the sheet.

Tested out the code and it appears to work fine in columns O and P. However, Columns R and S seem to be an issue. These columns contain Bloomberg formulas so I’m not sure if this affecting the formatting.

Tested the code separately on another sheet and seemed fine for columns containing checks. Bit of a mystery at the mo. ?‍

 

[jasonb75]

Is it copy and paste from web page? If so there could be stray spaces or zero width characters in the cell with the word 'check', this would make it similar but not equal so there would be no match.

 

[mrsushi]

Is it copy and paste from web page? If so there could be stray spaces or zero width characters in the cell with the word 'check', this would make it similar but not equal so there would be no match.

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

#VALUE!​