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]

Your code works for me:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
4
5															CHECK
6															CHECK
7															NOT
8															NOT
9															CHECK
10
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B5:O500	Expression	=$O5 ="CHECK"	text	YES

 

[jasonb75]

"CHECK" is >= 30 so the will be highlighted by the first procedure, you don't need both unless you want to highlight "CHECK" without highlighting >=30, in which case you need to delete any existing rules first.

 

[Dan_W]

And as to your second question, I've tweaked your code to use the last row method. Note that it uses column B as the anchor.

Sub FormatCheck()
Dim lRow As Long
lRow = Cells(Rows.Count, 2).End(xlUp).Row
With Range("$B$5:$O$" & lRow)
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$O5 =""CHECK"""
    .FormatConditions(1).Interior.ColorIndex = 37
End With
Set lastRow = Nothing
End Sub

 

[mrsushi]

Many thanks Jason for the tip to remove the formatting,

Thanks Dan for the lastrow code. Much appreciated.

 

[mrsushi]

And as to your second question, I've tweaked your code to use the last row method. Note that it uses column B as the anchor.

Sub FormatCheck()
Dim lRow As Long
lRow = Cells(Rows.Count, 2).End(xlUp).Row
With Range("$B$5:$O$" & lRow)
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$O5 =""CHECK"""
    .FormatConditions(1).Interior.ColorIndex = 37
End With
Set lastRow = Nothing
End Sub

One last question, how would the above code change if the "Check" was also applied to columns P, R and S?

 

[Dan_W]

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?

 

[Dan_W]

Many thanks Jason for the tip to remove the formatting,

Thanks Dan for the lastrow code. Much appreciated.

You're very welcome.

 

[mrsushi]

You're very welcome.

One last question Dan, how would the above code change if the "Check" was also applied to columns P, R and S?

 

[Dan_W]

One last question Dan, how would the above code change if the "Check" was also applied to columns P, R and S?

Sorry - I should have "replied" to that question directly so you could've seen that I had responded. Scroll up to message #7. I ask a few questions,