Conditional Formatting/Traffic LX style is not formatting correctly

[christinembater]

Hi All,
I'm more than new at VBA, literally just started about 2 months ago in a class, and I didn't even know VBA existed before this class. So, I've created a workbook that has a command button, that takes you to a userform. The user chooses a month, picks a date on the calendar, and enters data into several text boxes. I then have code that goes to the appropriate month tab (one for each of the 12 mo), enters the date in the appropriate cell, and enters the data into the appropriate cells. More code that then calculates the percentage of actual data vs. goal data (so cell D4 = C4/B4, in percentage form).

My question is, I've tried a few different ways to apply the conditional formatting of traffic light style to the percentage column (D4:D14), but it's not coming up correctly. It keeps coming out either mostly red or mostly green lights, even though you can clearly see that some of the cells are within that yellow color range.

Here's my code below. I cobbled it together from recording a macro and looking at that, plus mashed together with pieces I've found on the internet. I would greatly appreciate anyone pointing out my idiocy and fixing this. I have been stumped on this all weekend and can't seem to figure out why it's not working.

Thank you!!!

Please Login or Register  to view this content.

[FDibbins]

Hi and welcome to the forum

Why not use the built-in conditional formatting in excel?

[christinembater]

Hi there,
Well, I thought I had to write the code to tell Excel once there was a value there to apply the conditional formatting. Is there code to just turn the conditional formatting "on"? Or should I go to each month tab, select the range, and apply the formatting by hand, before the user sees the workbook?

I'm a super newbie at this whole thing, so I apologize if I'm asking obvious questions here.

[FDibbins]

Or should I go to each month tab, select the range, and apply the formatting by hand, before the user sees the workbook?

Yup, pretty much and from the sounds of it, maybe some of the other stuff you are programming with VBA could be done with native excel function?

More code that then calculates the percentage of actual data vs. goal data (so cell D4 = C4/B4, in percentage form).

Why not just have 1 data page that the user enters data into (below the previous entry), and then run all your cals etc on other sheets?