Previous Topic Next Topic

How to check % value in excel and macro using VBscript?

Solved/Closed
anjanasundaram Posts 5 Registration date Thursday February 11, 2016 Status Member Last seen February 25, 2016 - Feb 11, 2016 at 12:08 AM
anjanasundaram Posts 5 Registration date Thursday February 11, 2016 Status Member Last seen February 25, 2016 - Feb 11, 2016 at 06:54 AM
Want to check a cell with % percentage format matches the value in Vbscript code.

Sub ChangeColor()
Dim lRow
Dim myVar

lRow = Range("AC" & Rows.Count).End(xlUp).Row
Set MR = Range("AC1:AC100" & lRow)

For Each cell In MR

'Selection.NumberFormat = "%"
If cell.Value <= "60%" Then cell.Interior.ColorIndex = 3
If cell.Value >= "80%" Then cell.Interior.ColorIndex = 10



Next
End Sub

Using the above code to change the color in a sheet for the specific set of values. For values less than 60%, the color should be red, >60 and <=80 should be orange and >80 should be green. Can someone please help me with creating this Macro ?
Related:

2 responses

Answer 1 / 2
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Feb 11, 2016 at 03:51 AM
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.
2
anjanasundaram Posts 5 Registration date Thursday February 11, 2016 Status Member Last seen February 25, 2016
Feb 11, 2016 at 04:33 AM
Hi Coolio,

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
0
Answer 2 / 2
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Feb 11, 2016 at 06:00 AM
Hello Anjanasundaram,

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.
:-)
2
anjanasundaram Posts 5 Registration date Thursday February 11, 2016 Status Member Last seen February 25, 2016
Feb 11, 2016 at 06:54 AM
Thanks vcoolio :)
Appreciate your help on this. Your code is higly useful :)
0

Similar discussions

Lenovo Laptop blinking keys but no display
What123What -
What123What -

5 responses
How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses