Conditional Formatting

[gmazza76]

Good Afternoon,

I know I can change the colour etc... of a word or the cell formatt depending on what is shown in the cell.

ie. I know i can change a cell colour to red if it has "Overdue" showing in it.

But is there a way i can make the whole row of the sheet go a different colour instead of just the cell.

ie. if A2 is showing "Overdue" A2:Z2 is all in RED ( the cell colour and the text in white )

Many thanks
Gavin

 

[baitmaster]

when you apply conditional formatting, apply it to the whole row, use your formula in the normal way, but lock it to a specific column using $ like you would in a cell formula

 

[gmazza76]

Sorry about this, but i havent really done a lot of formating so where do you mean lock ( $ ) info as i usually use just set "cell value is equal" and go from there?

Many thanks
Gavin

 

[baitmaster]

OK, sorry. Im so used to using "use a formula" I forget there are other ways to do it...

Select say row 3. Assuming A3 is the active cell (i.e. the cell in row 3 that you can write into - this varies if you have scrolled across to the right) then you need, in your conditional format, the formula =$A3="overdue" to format whole row based on contents of cell A3

In case you dont know, using $ in cell references in your formula means that the formula does not change if you e.g. copy and paste a cell somewhere else - it remains fixed to a row, or a column, or both. Without it, the reference is relative to your current position
e.g.
A1 - relative
$A1 - column always locked, row relative
A$1 - column relative, row always locked
$A$1 - will always point at A1

The same approach works within conditional formatting, except you are effectively writing the format into a range of cells at the same time. Without the $, each cell will be looking at itself. Lock the formula with $, and it looks at cells according to the rules above

Hope this makes sense

 

[tagman]

I'm doing almost exactly the same thing, funnily enough on a sheet of overdue jobs. The formula to enter in the Conditional Format is

=$A1="Overdue"

where A1 is the cell you want to use as your condition. You can then do a format paint along the rows as necessary.

 

[gmazza76]

Thanks for this.

I have one issue where it seems to be leaving all the cells in the row with info in as white then filling in the blanks in yellow and after column Z where my data finishes.

Any ideas why it would be doing this???

Cheers

 

[tagman]

If you're pasting the format along the whole row it'll continue after the data ends. If you paste it down only the columns you need that will stop. I'm not sure what you mean about the cells with info in staying white though.

 

[gmazza76]

<TABLE style="WIDTH: 1892pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=2519 x:str><COLGROUP><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3328" width=91><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" width=88><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" width=88><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3474" width=95><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4278" width=117><COL style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 4425" width=121><COL style="WIDTH: 111pt; mso-width-source: userset; mso-width-alt: 5412" width=148><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4315" width=118><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" span=2 width=74><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><COL style="WIDTH: 48pt" span=6 width=64><COL style="WIDTH: 180pt; mso-width-source: userset; mso-width-alt: 8777" width=240><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" width=107><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 168pt; mso-width-source: userset; mso-width-alt: 8192" width=224><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 45pt" height=60><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #969696; WIDTH: 68pt; HEIGHT: 45pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=60 width=91>Overdue</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #969696; WIDTH: 65pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl29 width=86>Expected Return Date</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #969696; WIDTH: 66pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 width=88>On Returned</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; WIDTH: 66pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 width=88>Sent Date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; WIDTH: 71pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 width=95>Meter Operator</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; WIDTH: 88pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 width=117>Case ID / SAP Ref</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; WIDTH: 91pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 width=121>Query Catagory</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; WIDTH: 111pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 width=148>Query Sub Catagory</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; WIDTH: 89pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 width=118>MPAN</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; WIDTH: 83pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 width=110>House Number & Postcode</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; WIDTH: 56pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 width=74>New / Correct MSN</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; WIDTH: 56pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 width=74>Old / Incorrect MSN</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; WIDTH: 74pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 width=98>Exchange / Removal Date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 width=64>Meter Type</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 width=64>Meter Make & Model</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 width=64>Removal Read DAY</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 width=64>Removal read NIGHT</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 width=64>Install Read DAY</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 width=64>Install Read NIGHT</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; WIDTH: 180pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 width=240>Additional Information</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 width=64>Flows Needed</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 width=64>Your Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; WIDTH: 80pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 width=107>Team Manager</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 width=64>Business Area</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; WIDTH: 168pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 width=224>CMO Comments</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 width=64>Query Status</TD></TR><TR style="HEIGHT: 21.95pt; mso-height-source: userset" height=29><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 68pt; HEIGHT: 21.95pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl34 height=29 width=91>AWAITING</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 65pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl35 width=86 x:fmla="=SUM(D2+28)" x:num="40732">08 July 2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 66pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl34 width=88 x:num>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 66pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl36 width=88 x:num="40704">10/06/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 71pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl37 width=95 x:num="40697.456805555557">03/06/2011 10:57</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl33 width=117>1-9329191106</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 91pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl33 width=121>Update missing exchange details</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 111pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl33 width=148>Exchange took place after CMO appointed</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 89pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl33 width=118>2000021356607</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 83pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl33 width=110>168 BH12 2HL</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl33 width=74>F11R21162</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl33 width=74>F99C13400</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl38 width=98 x:num="40568">25/01/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl33 width=64>Credit single rate</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl33 width=64>Unknown</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl33 width=64>08533</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BACKGROUND: yellow; COLOR: windowtext; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: style; mso-pattern: auto none" class=xl33 width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl33 width=64>00015</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BACKGROUND: yellow; COLOR: windowtext; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: style; mso-pattern: auto none" class=xl33 width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 180pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl33 width=240>customer has confirmed meter was exhanged as above, please update records. current read 00261</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl33 width=64>Both D0150 & D0149</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl33 width=64>Sarah Whitehead</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 80pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl33 width=107>Nessie Brush</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl33 width=64 x:bool="TRUE">TRUE</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 168pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl33 width=224>BGAS</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl33 width=64>New</TD></TR></TBODY></TABLE>Afternoon,

It has appeared as above.
Removal Night / Install Night have come up in yellow where the rest of the cells in the row have stayed white.
Will it make any difference if "AWAITING" is the outcome of a sum and not just typed in

When i meant in white i meant the cells hadnt changed @ all just the 2 above

Cheers

 

[tagman]

It shouldn't make any difference. Just enter the formula I gave you and then use the Format Paint tool to enter across the columns. You might have a conditional format already on the cells that don't change which may overide the new one and that may stop the formatting working.

 

[gmazza76]

Sorry Again,

But what do you mean "format paint" Tool?

Gavin