Conditional Formatting

[jstawski]

Hello,

I am working on a workbook that deals with costs. I am trying to write code that will highlight a range of cells given the cost has surpassed 5%, 10% or 15% of projected costs. Example, if cost was 5% over, highlight the cell yellow; 10% over, orange; 15% over, red. I am having problems getting the code to highlight the correct cell, or to call the correct cell.

Code:

Please Login or Register  to view this content.

Range K10:K84 is money spent, M10:M84 is budgeted amount. L10:L84 serves no role in my calculations. When I run the macro, the cells selected and altered AFTER the first run through are outside the specified range. I am also having issues because K10:K12, L10:L12, and M10:M12 and K13:K15, L13:L15, and M13:M15 ...etc... K82:K84,L82:L84, and M82:M84 are merged cells.

Thanks, I can provide additional information, but cannot provide the workbook. I made a mock workbook without the code attached.

[rodrignj]

You should just use conditional formatting and set 3 conditions =if(M10=>(k10*.05),true,false) then when you go to manage the conditions you can apply it to any range you like.

[jstawski]

Thanks for the reply. I tried using conditional formatting but I am unaware how to use it with 3 separate conditions and a formula...

[rodrignj]

If you posted some sample data I could help you out more. Conditional formatting would eliminate the (marginal) burden of having to run a macro every time you add more date.

[jstawski]

The sample workbook that I attached should have a few sample inputs (not sure why, but the attachment is linked in the code above). I tried the following in conditional formatting:

Capture.PNGCapture1.PNG

This didn't work.

[rodrignj]

Can you try attaching that sample workbook again, you only have pictures posted here.

[jstawski]

Here it is:
Book1.xlsx

[rodrignj]

The problem you're having is that your formula is quoted, so the condition is looking for "IF(xyz...." instead of 4200. I also added a little bit to your formula to prevent unused, or "zero," cells from being highlighted, also you forgot the "=" sign. I've attached the workbook back and I have fixed your formulas. Everything you need is in the "Conditional Formatting Manager."

Hope this helps!


Book1.xlsx

[jstawski]

rodrignj, thanks! Much easier than what I was attempting.

[rodrignj]

Happy to Help