anjanasundaram

- Posts
- 5
- Registration date
- Thursday February 11, 2016
- Last seen
- February 25, 2016

- Posts
- 5
- Registration date
- Thursday February 11, 2016
- Last seen
- February 25, 2016

Related:

- How to check % value in excel and macro using vbscript?
- Import data dari excel ke excel dengan macro - Forum - Excel
- How to transfer data from one sheet to another in excel using macros - How-To - Excel
- Insert picture into excel cell macro - How-To - Excel
- How to create a calculator in excel using macros - How-To - Excel
- How to create graph in excel using macros - Forum - Excel

Best answer

vcoolio

- Posts
- 1214
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- January 21, 2019

Hello Anjanasundaram,

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

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:-

to

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 :-

This will hard code the range to just 100 rows.

I hope that this helps.

Cheerio,

vcoolio.

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.

A few words of thanks would be greatly appreciated. Add comment

4534 users have said thank you to us this month

vcoolio

- Posts
- 1214
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- January 21, 2019

Hello Anjanasundaram,

If you custom format the cells to 0.00%, the following works OK for me:-

Have a look at my test work book here:-

https://www.dropbox.com/s/mjvncboyx9sd1cf/Anjanasunduram%28conditional%20formatting%29.xlsm?dl=0

You can also trim the code drastically by referencing the whole range:-

Have a look here:-

https://www.dropbox.com/s/8gv4akpqcg94s9q/Anjanasunduram%282%2C%20conditional%20formatting%29.xlsm?dl=0

But, if you're happy with what you have come up with...................

Cheerio,

vcoolio.

:-)

If you custom format the cells to 0.00%, the following works OK for me:-

Sub ChangeColor() Dim lRow As Long Dim MR As Range Dim cell as Range lRow = Range("AC" & Rows.Count).End(xlUp).Row Set MR = Range("AC1:AC" & lRow) For Each cell In MR 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 If cell.Value = "" Then cell.Interior.ColorIndex = xlNone Next End Sub

Have a look at my test work book here:-

https://www.dropbox.com/s/mjvncboyx9sd1cf/Anjanasunduram%28conditional%20formatting%29.xlsm?dl=0

You can also trim the code drastically by referencing the whole range:-

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

Have a look here:-

https://www.dropbox.com/s/8gv4akpqcg94s9q/Anjanasunduram%282%2C%20conditional%20formatting%29.xlsm?dl=0

But, if you're happy with what you have come up with...................

Cheerio,

vcoolio.

:-)

A few words of thanks would be greatly appreciated. Add comment

4534 users have said thank you to us this month

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