Conditional formating

[Guenzak]

I have done this before and can't remember the formula or figure it out
again. I have a tabke the say is 10cells X 10 cells. In the table I want to
put the abbr. for cities such as DAL, HOU, DEN.

Now next to the chart I have 10 cells that I highlighted and named RED. In
the past I had set up a conditional formatting formula so that if the abbr
HOU in in one of the 10 cells that were calle red all of the cells in the
table that contained the abbr HOU would format its backround to red. It was a
formula that was set up as a conditional format for the 10x10 table and in
the formula it referenced the name of the group of cells that I hilighted and
renamed red. Can anyone here help me with this formula. This is the same
board where I found the formula a year or so ago but now I can't find it or
remember it

Thanks

 

[Vacation's Over]

try countif
countif(yourrow say A5:h5, abbr say "HOU")>0

conditionalformat
formula is -
=Countif(A5:H5,"HOU")>0

 

[Guenzak]

Thanks Vac but that isn't quite doing it. I am applying the conditional
format to colums ABCD rows 1 thru 10. What I applied was
=Countif(H1:H10,"HOU")>0
What I wanted to have happen is if "HOU" appeard in any cell H1 thru H10
then all of the cells in my main table (ABCD 1-10) that had "HOU" in it would
format i'ts background to red but it isn't quite working that way. Also I
have 40 different city abbr. so I have to replace "HOU" in the formula with
something that would represent "anything" so that I can use any of the 40
abbr. in cells H1:H10 to change the background of the cells with those cities
in the main table red. So if H1 had "CAR" H2 had "BUF" H3 had "DAL" then all
of the cells in my main table that had either CAR, BUF, or DAL would format
to a red background while all of the other cells with other cities remain
white.

you see my real table has over 400 cell with 40 different cities and a few
times a week I have to hilight different cities different colors. To change
the cell backgrounds manually takes hours. I want to be able to write the
abbr such as "DAL" in a specified colum to the right of my table (that colum
being H1 truH10 in the example above) and have all the cells in my 400 cell
table that have "DAL" in them to change red.

Thanks for any and all help!!!

 

[Rowan]

Select A110 so that A1 is the activecell (should be white wiht A1
shown in the Name box on the formula bar). Then apply the conditional
formatting using the formula:
=NOT(ISNA(VLOOKUP(A1,Red,0)))
where Red refers to your named range holding the abbreviations of cities
you want coloured red.

Hope this helps
Rowan

 

[Guenzak]

Rowan,

That formula is real close but isn't quite working correctly. I tried it and
in my table i put about 10 city abbr. Then in my named range I type "HOU"
but for some reason after I did that the cells with "hou" turn red as was
supposed to happen but so did the cells with "WAS", and "PIT". If I put in
"DAL" into my named range the cells in my table with "DAL" turn red but so do
the cells with "WAS", "PIT", and "DEN". So for some reason it is formatting
the correct cells but also identifying others as needing formatting. Formula
probably just needs a small correction but this formula is above my skill
level.
Please Help.....and Thanks!!!!

 

[Rowan]

Sorry, that's my mistake. Formula should read:
=NOT(ISNA(VLOOKUP(A1,Red,1,0)))

Regards
Rowan

 

[Guenzak]

Thank You, Thank You...a kingdom of Thank Yous