Excel 2002: Can I condition format block of cells ?
Mr. Low
I have the following table:
A B C
1 Y12 XXXX XXXX - Yellow
2 Y12
3 T24 - Green
4 T24
5 T24
6 H85 - Pink
7 H85
8 J74 - Yellow
9 M41 - Green
10 M41
11 M41
Is there anyway for me to condition format the table as illustrated? The
block need to change color only when refrence in column A channges. How
should the formula be ? If 3 colors are not possible what about two ?
Thanks
Low
--
A36B58K641
Satti Charvak
conditional formating can be done for 3 colors (conditions), besides the
default color (normally white)
--
Kind Regards,
Satti Charvak
Only an Excel Enthusiast
Noida, India
ShaneDevenshire
1. Because Excel 2003 and earlier only support a max of 3 colors we need to
know what version of Excel you are using?
2. Which cells do you want to format with the color - the titles in column
A, items in the other columns if they contain entries? or the entire row with
titles and data area, or the whole data area without the titles but including
blank cells?
3. You show two areas in green, what determines why you color these two the
same and not use a different color? In other words what factor is
controlling your color coding? Maybe just color every other different item
an alternating color - green, yellow, green, yellow....
--
Thanks,
Shane Devenshire
T. Valko
I can get you 2 colors.
Note that these formulas are fairly calculation intensive so this may not be
a good idea if you have 1000's of rows of data.
Based on your sample...
Select the range A1:C11
Goto the menu Format>Conditional Formatting
Condition 1
Select the Formula Is option
Enter this formula in the box on the right:
=MOD(ROUND(SUMPRODUCT(($A$1:$A1<>"")/COUNTIF($A$1:$A1,$A$1:$A1)),0),2)=0
Click the Format button
Select the desired style(s)
OK
Click the Add button
Condition 2
Select the Formula Is option
Enter this formula in the box on the right:
=MOD(ROUND(SUMPRODUCT(($A$1:$A1<>"")/COUNTIF($A$1:$A1,$A$1:$A1)),0),2)=1
Click the Format button
Select the desired style(s)
OK out
--
Biff
Microsoft Excel MVP
"Mr. Low" <lsk...@discussions.microsoft.com> wrote in message
news:B04D23F6-A29C-4745...@microsoft.com...
T. Valko
>I can get you 2 colors.
Actually, you can get as many as conditional formatting will allow (which is
3 in versions of Excel prior to Excel 2007).
Just change the MOD divisor to the number of colors you want and change the
comparison accordingly:
For 3 colors:
=MOD(.....,3)=n
Condition 1:
=MOD(.....,3)=0
Condition 2:
=MOD(.....,3)=1
Condition 3:
=MOD(.....,3)=2
--
Biff
Microsoft Excel MVP
"T. Valko" <biffi...@comcast.net> wrote in message
news:%23NaR0tc...@TK2MSFTNGP03.phx.gbl...
Mr. Low
Thanks for the formulas.
I needs to work on many rows of data perhaps 45,000, Is there any other
formula that could work well under this condition?
Thanks
Low
--
A36B58K641
T. Valko
slow.
The only other way that I can think of would use a helper column. Let's
assume your data looks like this:
..........A..........B
1.....header........
2........1.............
3........2.............
4........2.............
5........3.............
6........4.............
7........4.............
In B2 enter a "x".
In B3 enter this formula and copy down to the end of your data:
=IF(A3=A2,B2,IF(B2="x","y","x"))
You will end up with this:
..........A..........B
1.....header........
2........1...........x
3........2...........y
4........2...........y
5........3...........x
6........4...........y
7........4...........y
Then you can set 2 colors, one based on column B = x, the other based on
column B = y.
You can hide column B if you want, or you could use a column off to the
right of your data so that it's not displayed on the screen, say like column
AA.
--
Biff
Microsoft Excel MVP
"Mr. Low" <lsk...@discussions.microsoft.com> wrote in message
news:8C54CB65-7C54-4063...@microsoft.com...
Mr. Low
I think this is a smart way.
I will try it out.
Thanks
Low
--
A36B58K641
"T. Valko" wrote:
> 45k rows is too many for these formulas. Calculation would be extremely
> slow.
>
> The only other way that I can think of would use a helper column. Let's
> assume your data looks like this:
>
> ...........A..........B
> 1.....header........
> 2........1.............
> 3........2.............
> 4........2.............
> 5........3.............
> 6........4.............
> 7........4.............
>
> In B2 enter a "x".
>
> In B3 enter this formula and copy down to the end of your data:
>
> =IF(A3=A2,B2,IF(B2="x","y","x"))
>
> You will end up with this:
>
> ...........A..........B
T. Valko
--
Biff
Microsoft Excel MVP
"Mr. Low" <lsk...@discussions.microsoft.com> wrote in message
news:6AE1945C-1E04-4969...@microsoft.com...
fuoriclasse
I join the discussion a bit late but I had a similar problem and your
formula really helped. Many thanks T . Valko.
I am trying to understand the formula and I'm not sure what the
COUNTIF($A$1:$A1,$A$1:$A1) bit does.
Any chance you can enlight me?
In the sumproduct I understand the formula ($A$1:$A1<>""), you count the non
empty cells, but I dont really see whats happens after you divide by the
above.
Thanks
jrg...@gmail.com
>
> Select the range A1:C11
> Goto the menu Format>Conditional Formatting
> Condition 1
> Select the Formula Is option
> Enter this formula in the box on the right:
> =MOD(ROUND(SUMPRODUCT(($A$1:$A1<>"")/COUNTIF($A$1:$A1,$A$1:$A1)),0),2)=0
> Click the Format button
> Select the desired style(s)
> OK
>
> Click the Add button
>
> Condition 2
> Select the Formula Is option
> Enter this formula in the box on the right:
> =MOD(ROUND(SUMPRODUCT(($A$1:$A1<>"")/COUNTIF($A$1:$A1,$A$1:$A1)),0),2)=1
> Click the Format button
> Select the desired style(s)
> OK out
>
> --
> Biff
> Microsoft Excel MVP
this did not work for me. THe first condition is always true. I can
see that the help cell would work, but that seems particularly ugly to
me. If only the color of a cell (fore, back, etc) was available via
a simple function. I guess I'll stick with just setting the first
cell of a new set of values to a different color, which is easy.