Conditional Formating

  • Thread starter Thread starter shooter27
  • Start date Start date
S

shooter27

I have 400 rows of data x 20 columns of data. There are four columns that
contain any one of five different key letters (R, NR, O, U, NA). What I want
to do is that everytime a letter such as NR shows up in any of the four
columns, the entire row has a conditional format applied. When I do "Cell
Is", the conditional format is only applied to the one cell even if I have
the entire row(s) highlighted when I create the conditional format. I tried
to find similar posts to help but have not been successful. Using Excel 2003.
 
Highlight the four columns and use a conditional formatting that locks the
columns but not the rows, like so:

Condition1:
Formula Is: =OR($A1:$D1="NR")
....and set your color.
 
Your question says you wish to conditionally format a row when a match
appears in a column. I presume you mean you wish to conditionally format a
column when you match any of the five keys below in the column? If so:

=OR(COUNTIF(G:G,$I$1)>0,COUNTIF(G:G,$J$1)>0,COUNTIF(G:G,$K$1)>0,COUNTIF(G:G,$L$1)>0,COUNTIF(G:G,$M$1)>0)

Notes:
- My phrases to be matched were stored in I1:M1. You could hard code (e.g.
COUNTIF(G:G,"R") replaces COUNTIF(G:G,$I$1)
- The formula above formatted column G. You could limit (e.g
COUNTIF(G$2:G$25,"R")
- Build you format in one column (or row) and use the format painter to
cover the rest.
 
Back
Top