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

Question

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 ?

vcoolio · Accepted Answer

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.

vcoolio · Answer

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

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

2 responses

Similar discussions

Subscribe To Our Newsletter!