how to do the conditional formatting for this?

[Guest]

in column B, i have dates in the form 1/1/92 to 31/12/92, how do i write the
conditional formating, so that i can highlight particular months...for
example, all june will be red, or all December will be red?

 

[Alan Perkins]

Select conditional formatting, and under condition 1, select "Formula is",
and in the formula, type "=MONTH(B1)=6" (for June), and change the 6 to 12
for December.

HTH

Alan P.

 

[Don Guillett]

From a post of mine earlier today. Change to suit to

select case month(target)

Use a worksheet_event with select case to change your format,something like
this.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
With Target
Select Case Target.Value
Case Is >= 76
.Interior.ColorIndex = 3 '(red shade)
Case Is >= 52
.Interior.ColorIndex = 4 '(green shade)
Case Is >= 26
.Interior.ColorIndex = 46 '(orange shade)
Case Is >= 11
.Interior.ColorIndex = 41 '(blue shade)
Case Is >= 6
.Interior.ColorIndex = 6 '(yellow shade)
End Select
End With
End If
End Sub

 

[Guest]

Mmm, nothing happens, the dates are in the column B and there are about 2000
over rows. Should i put B3:B2000 or just B1? Both ways nothing happened.

 

[Don Guillett]

In case you can't figure it out.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 2 Or Target.Column <> 1 Then Exit Sub
On Error Resume Next
With Target
Select Case Month(Target)
Case Is = 1: .Interior.ColorIndex = 3
Case Is = 2: .Interior.ColorIndex = 6
'etc
Case Else
MsgBox "no date"
End Select
End With
End Sub

 

[Frank Kabel]

Hi
are you sure your date values are stored as 'real excel' dates and not
as 'Text'.

Also in your example:
- select B3:B2000
- goto the conditional format dialog
-and enter only
=MONTH(B3)=6

 

[Guest]

Mmmm, i typed in 1st january as "1-jan" but when i click on the cell, it
shows 1/1/92. So not sure whether this is text or dates?

 

[Frank Kabel]

Hi
you have to enter a valid date. That is WITH a year. You can then
afterwards format it as
D-MMM

 

[David McRitchie]

If you use B1 in the formula is B1 the active cell within your selection?
in other words select the entire B column with the top row visible
when you do that.

If you wanted to color entire rows you would use $B1 in the formula
=MONTH($B1)=6
and obviously choose a pattern color under format.

http://www.mvps.org/dmcritchie/excel/condfmt.htm