VBA Conditional formating

[Little Penny]

I'm trying to create a conditional format on about 150 rows in my
spreadsheet

Range("A4:L4").Select
Range("A5:L5").Select
Range("A6:L6").Select

Up to

Range("A150:L150").Select

Using these three condition

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$M$4=""xxxxx"""
Selection.FormatConditions(1).Interior.ColorIndex = 4
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$I$4=""yyyyy"""
Selection.FormatConditions(2).Interior.ColorIndex = 38
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$I$4=""zzzzzzzz"""
Selection.FormatConditions(3).Interior.ColorIndex = 40

(In the condition I would use the variable count to go to next row)

I tried creating a loop with a counter but I can't get my variable to
worth in the ranges.

Any help would be greatly appreciated.

 

[Mike Fogleman]

You can do this in one large selection and the formulas will auto-adjust the
references if you remove the $ from the formula as needed.
The $ makes the letter or number following it an Absolute reference. Since
you are formatting across multiple columns as well as rows, you have not
provided enough information about what you want for B4, C4, D4, etc. to give
you an exact example for your needs.
The following will format the entire range with each cell refering to the
row it is on, keeping columns M & I constant across the columns.

Sub test()
Range("A4:L150").Select

'Using these three condition
' Always use the following line first for Cond Frmtng
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$M4=""xxxxx"""
Selection.FormatConditions(1).Interior.ColorIndex = 4
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$I4=""yyyyy"""
Selection.FormatConditions(2).Interior.ColorIndex = 38
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$I4=""zzzzzzzz"""
Selection.FormatConditions(3).Interior.ColorIndex = 40
End Sub

Your 4 choices for the formula cell are:
M4
$M4
$M$4
M$4

Mike F

 

[Little Penny]

you have not
provided enough information about what you want for B4, C4, D4, etc

I would like the entire roe A4 B4 CE D4 E4 F4 G4 H4 I4 J4 K4 L4 M5 to
change color based on the conditions

A4 B4 CE D4 E4 F4 G4 H4 I4 J4 K4 L4 M5 base of condition
A5 B5 C5 D5 E5 F5 G5 H5 I5 J5 K5 L5 M5

down to

A150 B150 C150 D150 E150 F150 G150 H150 I150 J150 K150 L150

I hope this helps


Thanks

 

[Mike Fogleman]

I would like the entire roe A4 B4 CE D4 E4 F4 G4 H4 I4 J4 K4 L4 M5 to
Assuming M5 is a typo, I believe the code I gave does that. Each row, cells
A:L will all change color according to the value of column M & I in that
particular row only. Have you tried the code? If so, what results did you
get that is not desirable?

Mike F