shading rows and conditional formating

  • Thread starter Thread starter excelmedia
  • Start date Start date
E

excelmedia

Hello

I am trying to separate (by shading) my data according to th
infrmation in columns B, C and D which contain Day, start time and en
time.

I want to to shade every alternate block of rows that have the sam
info in B,C & D.

for example

A..B..C.. D
1 Mon 2PM 3PM
2 Mon 2PM 3PM
3 Tue 4PM 5PM
4 Wed 4AM 8AM
5 Wed 4AM 8AM
6 Wed 6AM 8AM


Therefore
Row 1&2 would be shaded
Row 3 clear
Row 4&5 shaded
Row 6 clear

Any suggestions....
Thank yo
 
Hi
try the following:
- select the cells of row 1 (only the first row)
- goto conditional format and enter the formula
=AND(B1=B2,C1=C2,D1=D2)
- choose a format and close the dialog

- select all other rows (starting in row 2)
- enter the following formula in the conditional format dialog
=OR(AND(B2=B3,C2=C3,D2=D3),AND(B2=B1,C2=C1,D2=D1))
- choose your format
 
Conditional formatting.

Select all of the rows with
In CF, change the condition to Formula Is and add this formula
=(OR(AND($A1=$A2,$B1=$B2,$C1=$C2),AND($A1=$A65536,$B1=$B65536,$C1=$C65536)))
select format>pattern and pick your colour (hint - light green<g>)
Voila.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob
the usage of A65536 is a nice trick (I had to use a separate formula
for row 1).

The OP may change your formula to
=(OR(AND($D1=$D2,$B1=$B2,$C1=$C2),AND($D1=$D65536,$B1=$B65536,$C1=$C655
36)))
if I understood his speicification correctly
 
Thanks Frank. What I did was to put the row1/row2 formula in in row 2 in CF,
copy up to row 1, and then pulled it out from CF. That way I ensured I got
the correct value (if you try and do it on a worksheet you get #REF).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
That's great, we all love a happy customer.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top