Conditional Formating for a row

[SVTF]

Please see post #4 for - includeds workbook

[FDibbins]

1st, avoid using entire columns or rows in CF, it will slow your file down.

2nd, try using absoluting onthe references...
=M4= ""
=$M4= ""

[SVTF]

Tried the above.

Perhaps I am chaning the wrong values based on whay I am looking to do.

Thoughts - Anyone else help me out?

[SVTF]

Ok revamped things ... this is for excel 2010

Still looking for some assistance which I assume is a very easy fix.

I have 4 rows with data in them ... to explain my issue.

Row 4 works as it should be with exception for column G? see below
Row 5 is not working the way I need it to
Row 6 works as it should be with exception for column G? see below
Row 7 works as it should be with exception for column G? see below

Request # 1

You will notice that the only difference when looking at the workbook is that I5 has no date (so it's a blank cell) which could happen
There is a counter in column L ... so L5 shows 71 ...

So when I5 happens to be blank and the counter in L - same row is > 14 I need for that entire row to turn red.

I need to add a condition to the already 3 that are there to make the above happen.

Request # 2

Column G...

Currently once the date is entered into I ... if < 14 the entire row turns green, if > 14 the entire row turns red, if the date is deleted from I then the row goes back to no color.

But for some reason I can not get Column G to work the same way as the other cells based on the above guide lines.

For these adjustments they need to run from Row 4:4999

See the attached workbook

Let me know if you have any questions

And Thanks in advance

[FDibbins]

1st, highlight all the rows in columns C:N, then select CF/Clear Rules/from selected cells

Then adjust the "Applies to" range in the CF in B2 to be the full range you want...
=$B$4:$N$4999

Note that if your range does not need to be that long, shortend it to what you actually need. CF can bocome a drain on resources and slow your file down

[SVTF]

FDibbins - Not sure your suggestion fixes what I need to do ... All other CF's are working fine.

Please review the revamped situation - Post # 4 with workbook example

[FDibbins]

Row 4 works as it should be with exception for column G? see below

for col G, you have the same CF rules in 6 times, plus it is referencing col N, not M. Is this meant to be a different rule?
Also, like I said, instead of applying CF to each column seperately, you can use the range of all the columns in the "applies to" box

If I understand the rest...
Like I said, remove ALL the cf rules you currently have in A and C:N, we will make all the rules in B, and have them Apply to the whole range.
click on B4 and go into CF
Change all the Applied to, to =$A$4:$N$4999. If G needs a different set of rules, then use this range instead =$A$4:$F$4999,$H$4:$M$4999
Remove the rule for turning white (=M4="") That is teh default color, you dont need a rule for that.
For the red, use this rule...
=AND($M4="",$L4>14,$A4<>"")
for the green rule, use this...
=$M4<=14
Note the use of absoluting $ to keep column M fixed for all rules

Now, for G, is that a different set of rules?

[SVTF]

Followed the above ... almost there ... couple of things came up.

Thanks by the way I was looking at this for a while and could not figure out your direction in your first post.

Did not see for what ever reason the duplicates in G & G does not need a different set of rules.
Also discovered the reason why there was a white CF statement it was because someone had the cells filled with green by default.

So almost there - please see below and see workbook

So I did everything you mentioned... couple of small tweaks.

• Row 5 & 7 should be red across the row over to M because L > 14 days
• Anytime L is over 14 days the row should turn red.

• The row does turn red if there is correction date in I and L is > 14 days - see row 9

Row 8 - I would like for it to be green when this situation comes up.

There are times where L could be equal to #N/A - this happens when the correction date is before the entry date.
When this happens it is considered complete because there is a correction date so the row needs to turn green over to M.

[FDibbins]

Row 5 & 7 should be red across the row over to M because L > 14 days

so you want the row to be red is L is >14, even if M has a value?. If so then modify the red formula to this...
=AND(or(iserror($L4,$L4>14),$A4<>"")
This should take care of the #N/A as well

[SVTF]

Please Login or Register  to view this content.

Error: Does not like ... too many arguments?

A row needs to be red for two reasons ...

If M is >14 days
and
If M is blank for > 14 days as per the value in L

The original formula suggested for red above in post #7 changes the row red if L > 14 and M is blank

M has the same value as L when there is correction date entered in I

So need to modify the red CF you provided (post #7) to include both reasons to turn red or need another CF to turn row red if M > 14

Regarding N/A

I added a third simple CF to handle the N/A

=$L4 less tha n 0

[SVTF]

@FDibbins when you have a moment can you have a look at my last hurdle.

[SVTF]

Anyone willing to put a fresh set of eyes on my request in post #10

Would like to close this up tonight.

Let me know if you have any questions.

See workbook in post #8

And Thanks in advance.

[FDibbins]

=AND(or(iserror($L4,$L4>14),$A4"")

oops I left off a ) sorry

=AND(or(iserror($L4),$L4>14),$A4"")

actually, try this instead...
=AND(or(iserror($M4),$M4=""),$L4>14,$A4<>"")

[SVTF]

@FDibbins,

Please Login or Register  to view this content.

Does not produce a different result that what we have already been getting ... still only turning the row red that does not have a value in M - Row 9

Row 5/7 are not being turned red even though the value in M is > 14

Not sure I this matters ... there is a formula in Column M and L ... not sure if it matters.

Thoughts?

[FDibbins]

OK, I have come up with this...
=AND($B4<>"",OR($M4>14,AND($B4<>"",$L4>14)))

If that does not work, then please lay out your rules for me again. I am obviously missing something. This is how I understand it...

turn red if
L > 14
M >14
M ="" L > 14

[SVTF]

Let me post the workbook still needs a tweak.

[SVTF]

Worked but didn't work ... See row 7 in the workbook

L is only 1 day and it turned the row red - should not turn red till day 15

Row only needs to turn red if

M > 14
M "" but L > 14

[SVTF]

OK, I have come up with this...
=AND($B4<>"",OR($M4>14,AND($B4<>"",$L4>14)))

That almost worked ... the trouble was that it was changing the row red when L < 14 and M was ""

I modified the above to

Please Login or Register  to view this content.

And for the moment appears to be working as needed.

Thank you for your help - marking as solved.

Rep Sent.

[FDibbins]

OK great You probably dont need the 2nd AND, could probably just use =AND($M4>14,$B4<>"",$L4>14), but as long as its working for you

[SVTF]

Thanks again ... cleaned it up as your above suggestion.