Clever Geek Handbook

Conditionally format one cell based on the values ​​in the column

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

enter image description here

Any ideas how to do this?

+3


source to share


3 answers


Please format B2 green with standard padding, 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 match and refers to B2.

+3


source


Assuming you are using Excel 2007 onwards:

  • Set cell shading for B2 = Green
  • Select cell B2 and select the menu item Conditional Formatting

    >New Rule

  • Use a formula to determine which cells to format

  • In the formula bar, enter =ISNUMBER(MATCH(B2,$L$6:$L$100,0))

  • Change the shade to red for format


Obviously change $L$6:$L$100

to what you need, but that should do it ...

+1


source


How to break it down into two steps.

Try putting a simple formula in Cell B1

:

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

This will return TRUE

if there is a match and FALSE

if there is no match. Then create two conditional rules based on Cell B1

.

+1


source






More articles:


All Articles