Hello,

im working on xl sheet which contain 12 months information. i need to
highlight the next column to the date according to the month. If the
date in cell A2 between 1/1/09 to 31/01/08 the next cell B2 should be
highlighted in red. If date in cell A3 between 1/5/09 to 31/05/09 then
the next cell should be highlighted in yellow. Same applies from
january till december. i checked in conditional format but only have 3
options. I know it can be done in VB but dont know the formula. Can
any one help me, please?

cheers

dave

Right click the tab, click View Code, and paste this code into the window
that opens:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
Dim d1 As Date
Dim d2 As Date

d1 = CDate("March 1, 2008")
d2 = CDate("January 1, 2009")


If Not Intersect(Target, Range("B1:B10")) Is Nothing Then
Select Case Target
Case d1 To d2
icolor = 3
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub


HTH,
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"dave" wrote:

Hello,

im working on xl sheet which contain 12 months information. i need to
highlight the next column to the date according to the month. If the
date in cell A2 between 1/1/09 to 31/01/08 the next cell B2 should be
highlighted in red. If date in cell A3 between 1/5/09 to 31/05/09 then
the next cell should be highlighted in yellow. Same applies from
january till december. i checked in conditional format but only have 3
options. I know it can be done in VB but dont know the formula. Can
any one help me, please?

cheers

dave
.

On Dec 5, 6:53*pm, ryguy7272
wrote:
Right click the tab, click View Code, and paste this code into the window
that opens:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
Dim d1 As Date
Dim d2 As Date

d1 = CDate("March 1, 2008")
d2 = CDate("January 1, 2009")

* * If Not Intersect(Target, Range("B1:B10")) Is Nothing Then
* * * * Select Case Target
* * * * * * Case d1 To d2
* * * * * * * * icolor = 3
* * * * * * Case Else
* * * * * * * *'Whatever
* * * * End Select
* * * * Target.Interior.ColorIndex = icolor
* * End If
End Sub

HTH,
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''..



"dave" wrote:
Hello,

im working on xl sheet which contain 12 months information. i need to
highlight the next column to the date according to the month. If the
date in cell A2 between 1/1/09 to 31/01/08 the next cell B2 should be
highlighted in red. If date in cell A3 between 1/5/09 to 31/05/09 then
the next cell should be highlighted in yellow. Same applies from
january till december. i checked in conditional format but only have 3
options. I know it can be done in VB but dont know the formula. Can
any one help me, please?

cheers

dave
.- Hide quoted text -

- Show quoted text -

Hello,

Thats fantastic but i got a little probs here. When i enter the date
its highlighted that cell but i want the cell next to the date to be
highlighted. For example when i entered date in cell A3, the next cell
which is B3 should be highlighted in yellow. how to do this?

Thanks in advanced.

dave