Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Guenzak
 
Posts: n/a
Default Conditional formatting...cont. from 9/25

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


"Rowan" wrote:

Select A1:D10 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" wrote:

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


"Vacation's Over" wrote:



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

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

"Guenzak" wrote:

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

Thanks
--
Guenzak


  #2   Report Post  
bj
 
Posts: n/a
Default

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

"Guenzak" wrote:

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


"Rowan" wrote:

Select A1:D10 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" wrote:

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


"Vacation's Over" wrote:



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

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

"Guenzak" wrote:

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

Thanks
--
Guenzak


  #3   Report Post  
Guenzak
 
Posts: n/a
Default

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


"bj" wrote:

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

"Guenzak" wrote:

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


"Rowan" wrote:

Select A1:D10 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" wrote:

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


"Vacation's Over" wrote:



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

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

"Guenzak" wrote:

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

Thanks
--
Guenzak


  #4   Report Post  
bj
 
Posts: n/a
Default

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



"Guenzak" wrote:

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


"bj" wrote:

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

"Guenzak" wrote:

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


"Rowan" wrote:

Select A1:D10 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" wrote:

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


"Vacation's Over" wrote:



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

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

"Guenzak" wrote:

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

Thanks
--
Guenzak

  #5   Report Post  
Guenzak
 
Posts: n/a
Default

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".


--
Guenzak


"bj" wrote:

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



"Guenzak" wrote:

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


"bj" wrote:

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

"Guenzak" wrote:

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


"Rowan" wrote:

Select A1:D10 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" wrote:

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


"Vacation's Over" wrote:



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

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

"Guenzak" wrote:

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

Thanks
--
Guenzak

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional Formatting Error ddate Excel Worksheet Functions 0 May 5th 05 09:00 PM
difficulty with conditional formatting Deb Excel Discussion (Misc queries) 0 March 23rd 05 07:13 PM
conditional formatting question Deb Excel Discussion (Misc queries) 0 March 23rd 05 03:07 AM
Determine cells that drive conditional formatting? Nicolle K. Excel Discussion (Misc queries) 2 January 7th 05 02:08 AM
Conditional formatting not available in Excel BAB Excel Discussion (Misc queries) 2 January 1st 05 04:33 PM


All times are GMT +1. The time now is 07:07 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"