Conditional Formatting a Formula

Question

Running a conditional formatting formula =LEFT(Q2, 7)="No Attendance" with cell range of =$Q$2:$GJ$302 that will change cell color to black (ie - column R2 will be black because it has the words "No Attendance"). 

What conditional formatting can I use so that the entire column (ie - R3:R320) will be black based upon the above formula and cell range?

Answer 1

ladr407 wrote:

The conditional formating while using this formula will highlight the cells within a row 'black', however, how do I get the entire column to highlight black?

Ah, by following instructions.  Sorry for being so blunt, but I did already provide the necessary instructions.  Let me be more specific (and simplify).

• Select either the columns Q:GJ or the range Q2:GJ302.  One way to do that:  put the desired range into the Name Box.
• Click on Format > Conditional Formatting.
• Select Formula Is from the left-most pull-down menu.
• In the right-most field, put the following formula (copy-and-paste from this response):   =COUNTIF(Q$2:Q$302,"No Atte*")>0.
• Click on Format, and select the format you want when the condition is true.
• For example, click on Pattern and select black.  (I would choose a different color.)
• Finally, click OK.

Testing....  Enter "No Attendance into any of Q2:Q302.  The entire column or ranged (depends on step 1) should change according to the conditional format selected in step 5.  Try the same in any of T2:T302.

Caveat:  In XL2003 SP3 on WinXP, the effect is not immediate(!).  Initially, the conditional format was applied to only some cells in the column.  Eventually, it applied to all cells in the column.  What worked most consistently:  minimize, then maximize the Excel window.  This is a defect.  It might be fixed in XL2007/Win7 and later.

Answer 2

The conditional formating while using this formula will highlight the cells within a row 'black', however, how do I get the entire column to highlight black?

Select the cells you want to be controlled by the formula, say H5:J10, and note the active cell within that selection, say it is H5 (note the conditionly formatted cells do not have to include the "trigger" cell, Q2 in your case), then use this formula in the Conditional Formatting dialog...

=LEFT($Q$2,7)="No Atte"

Note the $ signs making the cell reference absolute.

---

NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.

Answer 3

ladr407 wrote:

1.  How about =LEFT(Q2, 7)="No Attd"

The left 7 characters of "No Attendance" are "No Atte".  So you would use LEFT(Q2,7)="No Atte".  But why look at only the left 7 characters.  If the entire cell is "No Attendance", it is simpler and more efficient to write Q2="No Attendance".

Ladr407:

2.  If any "No Attd" found anywhere within the range $Q$2:$GJ$302 any column with the text "No Attd" will fill in with the color black

I'm not sure you mean what you write, or your meaning is unclear.

By "fill in with black", do you mean select black from Pattern; or do you mean select blank Font?

If you select blank Pattern, and if you use the default font color (also black), the result will be invisible text in the cell.  You might as well leave the cell blank with the black Pattern.

But I cannot imagine why you would an entire column to have a blank Pattern (background) if the font color is also black.  Or have you changed selected a non-default font color?

In any case, the following condition is true when "No Atte" is found at the beginning of any cell in column Q:

=COUNTIF(Q$2:Q$302,"No Atte*")>0

Note the use of mixed references.

You can select the column, enter that in the formula for Conditional Formatting for the column, then use Format Painter to copy the format across columns R through GJ.

Or you can select Q2, enter that in the formula for Conditional Formatting for the cell, then use Format Painter first to copy the format down through Q302, and finally select Q2:Q302 and use Format Painter to copy the format across R2:R302 through GJ2:GJ102.

Answer 4

1.  How about =LEFT(Q2, 7)="No Attd"

2.  If any "No Attd" found anywhere within the range $Q$2:$GJ$302 any column with the text "No Attd" will fill in with the color black

Answer 5

Running a conditional formatting formula =LEFT(Q2, 7)="No Attendance" with cell range of =$Q$2:$GJ$302 that will change cell color to black (ie - column R2 will be black because it has the words "No Attendance"). 

What conditional formatting can I use so that the entire column (ie - R3:R320) will be black based upon the above formula and cell range?

=LEFT(Q2, 7)="No Attendance"

That will NEVER be true because No Attendance is 13 characters long.

You say you want the entire range R3:R320 to be black (fill color?) based on the above formula and range.

Well, we know the formula needs to be corrected but what about the range?

Do you mean if the text string "No Attendance" is found ANYWHERE within the range $Q$2:$GJ$302 then make the ENTIRE range R3:R320 black (fill color?) ?

--

Biff

Microsoft Excel MVP