Row highlight based on the 1-31 day of the month

[Julian]

I've got a table with dates displayed in the first column (ascending order).
I would like to highlight each row based on the day of the month with one of
5 chosen colours (red, yellow, green, blue and orange).

rows with the 1st of the month: red
rows with the 2nd of the month: yellow
.......
rows with the 6th of the month: red
.......

Julian.

 

[guitarSevilla]

You would use conditional formatting to display different colors based
upon the day value.

Your conditional formatting formula would look at the day value of the
date. You will have multiple conditions for each row.

Condition 1
Formula is: =DAY(A1)=1, then format the cell to red

Condition 2
Formula is: =DAY(A1)=2, then format the cell to yellow

etc.

Hope this helps

 

[Julian]

The problem is I only get the first cell highlighted. How do I get the whole
row highlighted coresponding to the first cell where the date is located?
Julian

 

[Gord Dibben]

Select the entire rows by clicking on the row headers 1 to 31

Then enter the formula for A1 as so =DAY($A1)=1

NOTE:this would be 31 conditions...................only available in 2007 or
later.

But wouldn't it be faster to manually color rows 1 to 7 in your seven colors
then copy that formatting with the format paintbrush.


Gord Dibben MS Excel MVP

 

[Julian]

What my table consists of is an exported mobile telephone log. It has got
30-40 entries of missed calls, dialled numbers etc for each day. I'm just
trying to highlight each day (1-31) of the month with a different colour
(out of 5 colours), so it would be easier to be read and examined.

Julian.

 

[Julian]

So, I managed to add an extra column B with days of the week (Monday etc)
coresponding to the dates in column A (copied column A that paste and apply
the formula =A1 and custom format as "dddd" as per Ron Rosenfeld).
..
Now, how can I format highlight rows based on the 7 days on the week in
column B. that would be 7 formulas instead of 31 as u said.

Julian.

 

[Gord Dibben]

No need for an extra column.

You have a column of dates.

You want to color rows by weekdays 1 through 7

Use the WEEKDAY function which returns 1 to 7

Select those rows and Conditional Format.

Use a formula........................=weekday($a1)=1 give it a color.

With no change in selection of rows.

CF use a formula................=weekday($a1)=2 give it a color

When weekday(a$1) = 1 through 7 have been set you are done.


Gord

 

[Julian]

Works a treat.
Cheers.
Julian.

No need for an extra column.

You have a column of dates.

You want to color rows by weekdays 1 through 7

Use the WEEKDAY function which returns 1 to 7

Select those rows and Conditional Format.

Use a formula........................=weekday($a1)=1 give it a color.

With no change in selection of rows.

CF use a formula................=weekday($a1)=2 give it a color

When weekday(a$1) = 1 through 7 have been set you are done.


Gord

 

[Gord Dibben]

Good to hear.

Originally I thought you wanted a new color for each day of the month.

Not reading properly..................bad fault of mine which others have
pointed out<g>


Gord