CODEWITHSUNDEEP
exceldatetimeexcel-formulaworksheet-functionconditional-formatting
mtcrts70
mtcrts70

Reputation: 35

Conditionally format one cell based on values in a column

The user enters a date/time into cell B2. If this matches one of the values in column L I would like to format cell B2 as red, else format cell B2 as green.

enter image description here

Any idea how to do this?

Upvotes: 3

Views: 598

Answers (3)

JasonAizkalns
JasonAizkalns

Reputation: 20473

What about breaking this up into two steps.

Try putting a simple formula in Cell B1:

=ISNUMBER(MATCH(B2, L:L, 0))

This will return a TRUE if there's a match and a FALSE if there is not a match. Then make two conditional rules based on Cell B1.

Upvotes: 1

John Bustos
John Bustos

Reputation: 19574

Assuming you are using Excel 2007 onwards:

  1. Set cell shading for B2 = Green
  2. Select Cell B2 and select the Conditional Formatting > New Rule menu item
  3. Use a formula to determine which cells to format
  4. In the formula bar, put in =ISNUMBER(MATCH(B2,$L$6:$L$100,0))
  5. For format, change shading to Red

Obviously change the $L$6:$L$100 to suit you, but that should do it...

Upvotes: 1

pnuts
pnuts

Reputation: 59485

Please format B2 green with standard fill, then apply the following Use a formula to determine which cells to format, Format values where this formula is true: 

=match(B2,L:L,0)>0

with formatting (red) to suit and Applies to B2.

Upvotes: 3

Related Questions