Problem with Conditional Formatting

[Kemidan2014]

I have a spreadsheet which I Macro copy items over based on criteria and we wanted to add visual management to the rows of data based on comparing todays date with dates filled in the record

I have 3 sheets that copy data over based on different Supplier code but I hand keyed in the same rules into each sheet

Heres whats funny, on 2 of the sheets I am getting random rows being filled in with color that have no data in them and i dont get why they would getting filled in
I have tried selecting and clicking "clear contents" to no avail.

Heres my rules in order

=ISBLANK($A2) Set format to have no color fill
=AND($J2<=Today(),$J2>=(Today()-5)) Set color to yellow. this supposed to high light record due if today is with in 5 days of due day
=NOT(ISBLANK($M2)) Set color to Green if there's a date in that column
=$J2<=Today() Set color to Red if Todays date is greater than when the Plan is due meaning we are late

 

[Joe4]

Heres whats funny, on 2 of the sheets I am getting random rows being filled in with color that have no data in them and i dont get why they would getting filled in

Whenever I see something like this, the usual cause of the issue is the following: the rule is not being applied to the correct range.

I often see people get this wrong when trying to apply a CF formula rule to a range of cells at once. The range that they select and the CF formula they enter are not coordinated with one another.
When creating a CF formula rule for a range of cells at once, you MUST write the formula as it applies to the very first cell in your range selection.
As long as you have applied the correct absolute and relative range references in your formula (not doing this is another common cause of errors), Excel will automatically adjust it for all the other cells in the selection.

Here is a common example of this mistake that people make.
They will select an entire column at once, like column C.
Then they will enter in a CF formula. But let's say that row 1 is a header row that they want to skip. So they enter in a formula like this:

=C2="Yes"

However, this will cause all the CF formatting to be off by one row because when you select the entire column, C1 is the first cell in that selection (not C2).

So the formula:

=C2="Yes"

is being applied to cell C1, and

=C3="Yes"

is being applied to cell C2, etc

So this obviously is not correct. There are two options here.
One is that instead of selecting the whole column at once, to choose specific cells in that column starting with row 2.
Or, you can select the whole column, and then amend your formula like this:

=AND(C1="Yes",ROW()<>1)

which will skip over row 1, regardless of the value in it.