VBA to color cells
Posted on 2013-09-05
I am needing to color cells in a range M4 thru Z for as many rows that have values.
If M4:Z is between 10 and 20 then #CCCCFF
IF M4:Z is between 20 and 30 then #800080
IF M4:Z is between 30 and 40 then #000080
Because of the different colors - conditional formating does not remember the custom color soI can use it on a different sheet. That is why I am using VBA.
Sub ChangeColor() ' for sheet 1
lRow = Range("M" & Rows.Count).End(xlUp).Row
Set MR = Range("M4:Z" & lRow)
For Each cell In MR
If cell.Value = <10 Then cell.Interior.ColorIndex = 10
If cell.Value = between 10 and 20 Then cell.Interior.ColorIndex = 9
If cell.Value = between 20 and 30 Then cell.Interior.ColorIndex = 8
Thanks in advance