Conditional Formatting, Changing Colours to rows

[MatthewIJClark]

I'm setting up a spreadsheet the will require the row to change different colours when certain information is entered.

I.E. in E1 cell we enter a 10 digit reference number I need to the entire row to change to yellow.
in Q1 cell we enter a date and need the entire row to change to green, font style bold and font colour white.

I've tried using the following formula for the Q1 cell:

=$N2<=TODAY()

But this changes every line insteand of just the lines with a date entered in cell Q1.

Any help will be greatly appreciated.

[Special-K]

Maybe
=AND($N2<=TODAY(),$N2<>0)

[Fotis1991]

=and($q$1<>"",$N2<=TODAY()) ??

[Special-K]

You need to clarify things a little

you mention E1 then start talking about Q1 and finally you give an example of N2. . .

. . .but you dont say how these relate to each other.


hence Fotis and mine's similar but not totally identical suggestions

[Fotis1991]

You need to clarify things a little

you mention E1 then start talking about Q1 and finally you give an example of N2. . .

. . .but you dont say how these relate to each other.


hence Fotis and mine's similar but not totally identical suggestions

+1 to this.

[MatthewIJClark]

Maybe
=AND($N2<=TODAY(),$N2<>0)

Hi Special K,

This has worked for the date brilliant. Just need one for the 10 digit refere number.

Thank you once again.

[MatthewIJClark]

Hi Special K,

This has worked for the date brilliant. Just need one for the 10 digit refere number.

Thank you once again.

Unfortunately, this worked some what but when you delete the date from the cell the row doesn't revert back to it's original colour. Mmmmmmmmmmm........

This is slowly becoming the bane of my life.

[jhren]

If you want the line to change when date is entered in Q1, then the CF formula must reference Q1, or another cell derived from the value of Q1 and changes with each line. For example, you are referencing $N2, which is fine if it changes based on the Q1 entry, and $N3 is based on Q2 entry, and so forth.

You also want the line to change based on E1 entry. You will have to give one priority over the other, E1 or Q1 via N2.

[MatthewIJClark]

O.K. I think I better start from the beginning here and see if I'm on the right track.

I've got a spreadsheet that's 21 coloums of data and anything up to 1,000 rows, I've put together that depending on certain criteria entered into specific cells the entire row needs to change colour, they are as follows:

1) Row to change red when future date entered in cell in column N, currently using conditional formatting: =$N2<=TODAY().
2) Row to change orange when "In Stock" entered in cell in column N, currently using conditional formatting: =AND($N2="In Stock").
3) Row to change blue and font sytle to be bold white text when "CANC" entered in cell in column N, currently using conditional formatting:=AND($N2="CANC").

So these are currently working and if you change anything in the cells the conditonal formatting is looking at they revert to the previous colour and visa versa. I.E. enter "In Stock" over the furture date and the row changes orange, enter a furture date over the "In STock" and the row reverts back to red.

Now I've got 2 more colours I need to include, yellow and green with white bold text, these will use the following criteria on the spreadsheet:

4) In column E on the spreadsheet we enter in a 10 digit reference number, when this is entered I need to row to change yellow.
5) In column Q on the spreadsheet we enter a date in the past, when this is entered I need the row to change green with bold white text.

I would love to be able to upload what I've done so far but this is for my work and not personal use.

Thanks for any help given its much appreciated.

[jhren]

Does you sheet have headers? I'm just wondering be cause we're discussing CF applied to first row.

What is row color with no conditional formatting kicked in?

CF formula for row color by E: =NOT(isblank($E1) ...perhaps. Depends on whether it is actually blank until 10 digit ref# is entered. Also, do you want it to check whether the entry is number and 10 digits?

CF formula for row color by Q: =isnumber($Q1) ...perhaps, unless you want to verify it is a date and in the past?

You will also have to determine priority voer/under other CF conditions. Move up or down in manager, and determine whether stop if true is desired.

[MatthewIJClark]

Yes the spreadsheet does have headers on each coloumn.

Might be an idea to build into the CF that it is a 10 digit number entered, if this is easy please advise.

With regards to the date doesn't have to verify it as a past date as on the odd occasion the date entered could be today's date.

Thanks for all your help on this.

[MatthewIJClark]

O.K. just tried the CF =NOT(isblank($E1) but got the following error:

Your formula is missing a parenthesis--) or (. Check the formula, and the add the parenthesis in the approriate place.

Also tried, =isnumber($Q1) for the date and yes it seemd to work on some rows, it also changed the colour on rows with no date entered........

[jhren]

Sorry about that. =NOT(isblank($E1))

Checking for 10 digit # in E: =IF(LEN(TEXT(E2,"0"))<>10,1,0)

Apply only to column E and stop if true, positioned in manager above the row CF based on E. Format as desired, I usually go cell red, cause it'll show as soon as the number is entered wrong. You could get really picky and check that it is a number too.

[jhren]

.... You could get really picky and check that it is a number too.

=if(len(text(if(isnumber(e2),e2,0),"0"))<>10,1,0)

[jhren]

Why does this forum automatically change caps to lower case, such as in my last post? Does it have to do with starting a paragraph with "="???

[jhren]

.... You could get really picky and check that it is a number too.

=if(len(text(if(isnumber(e2),e2,0),"0"))<>10,1,0)

Should also note that the 10 digit# check isn't error proof. For example, I could enter the number 1235545604.78798 and it would pass the test.

I'll have to think on that... (but no guarantee I will)

[jhren]

Should also note that the 10 digit# check isn't error proof. For example, I could enter the number 1235545604.78798 and it would pass the test.

I'll have to think on that... (but no guarantee I will)

This will check for decimal: =IF(OR(IFERROR(E2-INT(E2)>0,1),LEN(TEXT(IF(ISNUMBER(E2),E2,0),"0"))<>10),1,0)

[FDibbins]

I dont see what you mean?

edit: OK that was all caps, I guess there it does drop to lower case

[newdoverman]

I have what I think is most of what you want but have hit a "wall" with the last one for some reason. Maybe someone else can take it further.

[MatthewIJClark]

I have what I think is most of what you want but have hit a "wall" with the last one for some reason. Maybe someone else can take it further.

Hi newdoverman,

Many thanks for your attached spreadsheet which helped greatly, now only need 1 more thing and I'll be a happy person.

In the same spreadsheet we enter a date in the past when the record was complete in column Q, I need this to change colour once the date has been entered and not before, I've tried the following CF formats:

=LEN($Q2)<TODAY()
=$Q2<=TODAY()

But both do the same thing and change the colours on all rows of the spreadsheet whether a date has been entered or note. I just want rows with the date entered in coloumn Q2 to change this colour. The date will always be in the past and never be today's date.

Cheers

Mattie

[newdoverman]

That is the problem that I couldn't get around. The entire worksheet would change colour even if I entered rules to have no formatting unless there was data in the rows. I couldn't find a reason for it and therefore couldn't stop it.

[MatthewIJClark]

That is the problem that I couldn't get around. The entire worksheet would change colour even if I entered rules to have no formatting unless there was data in the rows. I couldn't find a reason for it and therefore couldn't stop it.

Same as, it's driving me around the bend.

Only way I can get round it at the moment is to make column W a Wingdings text formt, put a tick in this column where we enter the date and use the following CF:

=$W2:$W1000="ü"

Selecting about 1000 rows before performing the above, it works but not how I originally planned.

[jhren]

Try =AND($Q2<TODAY()+1,YEAR($Q2)>1980) ...where you can replace 1980 with the oldest year of record, and prevents blank cells or some, not all, errant number entries.

[newdoverman]

Try this. It works here.