Hello Anjanasundaram,

I've modified your code a little as follows:-

Sub ChangeColor()
Dim lRow As Long
Dim MR As Range
lRow = Range("AC" & Rows.Count).End(xlUp).Row
Set MR = Range("AC1:AC" & lRow)
For Each cell In MR
NumberFormat = "%"
If cell.Value <= 60 Then cell.Interior.ColorIndex = 3
If cell.Value > 80 Then cell.Interior.ColorIndex = 4
If cell.Value > 60 And cell.Value <= 80 Then cell.Interior.ColorIndex = 45
Next
End Sub

and it should do as you require.

When declaring your variables, you need to also declare what type they are. I've done this for you above. I've also removed the myVar variable as I can't see where you are using it and changed MR to a range type variable.

I've also refined this line:-

Set MR = Range("AC1:AC100" & lRow)

to

Set MR = Range("AC1:AC" & lRow)

as you are using the entire column as your range, not just the fixed range "AC1:AC100". If the fixed range is all that you need, then you can change it to :-

Set MR = Range("AC1:AC100")

This will hard code the range to just 100 rows.

I hope that this helps.

Cheerio,

vcoolio.

Thanks for your assistance. The issue is the value in cells are in % format and this code does not check properly. It just colors all the cells as red for all the values.

I have modified my code as below and it works.

Sub ChangeColor()

lRow = Range("AC" & Rows.Count).End(xlUp).Row

lRow1 = Range("AD" & Rows.Count).End(xlUp).Row

lRow2 = Range("AE" & Rows.Count).End(xlUp).Row

Set MR = Range("AC1:AC100" & lRow)

Set MT = Range("AD1:AD100" & lRow1)

Set MQ = Range("AE1:AE100" & lRow2)

For Each cell In MR

'Range("MR").NumberFormat = "0.00%"

'Selection.NumberFormat = "#%"

If cell.Value <= 0.6 Then cell.Interior.ColorIndex = 3

If cell.Value > 0.6 And cell.Value <= 0.8 Then cell.Interior.ColorIndex = 45

If cell.Value > 0.8 And cell.Value <= 1 Then cell.Interior.ColorIndex = 10

If cell.Value = vbNullString Then cell.Interior.ColorIndex = 2

Next

For Each cell In MT

If cell.Value <= 0.6 Then cell.Interior.ColorIndex = 3

If cell.Value > 0.6 And cell.Value <= 0.8 Then cell.Interior.ColorIndex = 45

If cell.Value > 0.8 And cell.Value <= 1 Then cell.Interior.ColorIndex = 10

If cell.Value = vbNullString Then cell.Interior.ColorIndex = 2

Next

For Each cell In MQ

If cell.Value <= 0.6 Then cell.Interior.ColorIndex = 3

If cell.Value > 0.6 And cell.Value <= 0.8 Then cell.Interior.ColorIndex = 45

If cell.Value > 0.8 Then cell.Interior.ColorIndex = 10

If cell.Value = vbNullString Then cell.Interior.ColorIndex = 2

Next

End Sub