Conditional formatting...cont. from 9/25

[Guest]

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!!!!


--
Guenzak


:

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


:

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 highlight 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 column to the right of my table (that
column 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!!!


--
Guenzak


:



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

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

:

I have done this before and can't remember the formula or figure it out
again. I have a table 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 called 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 highlighted
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

 

[Guest]

try
serlecting A1 for one color
A2 for another color
Highlite your "Red" cells

in conditional
just enter
=$A$1
and format
in second conditional
=$A$2
and format

 

[Guest]

Thanks BJ but i don't see how that would accomplish what I'm trying to do???
Can you explain if I'm missing something?
--
Guenzak

try
serlecting A1 for one color
A2 for another color
Highlite your "Red" cells

in conditional
just enter
=$A$1
and format
in second conditional
=$A$2
and format

 

[Guest]

You aren't missing anything, I read your intial request wrong, sorry.

the equation in the conditional format should work.

chck the ther range name RED is just =$H$1:$H$10
and check that h1 to h10 actula do not contain anything

 

[Guest]

I verified that the range named red is just H1 thru H10 and also verified
that they did not contain anything previously but the conditional formula
=NOT(ISNA(VLOOKUP(A1,Red,0))) is still ging me the same problem. If I add
"DAL" (for example) to one of the cells H3 (for example) all of the cells in
my main table with "DAL" in then format to red but so do ones with some other
city abbr in them such as "WAS" or "PIT".