Colors according to the month

[dave]

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

 

[ryguy7272]

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--

 

[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''..






- 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

 

[p45cal]

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

This is a bit confusing, so clarification required..
your subject title is 'Colors according to the month'
You cite dates 'between 1/1/09 to 31/01/08'; one date is in 2008 th
other in 2009 - is this intended?
From the next bit I can glean that May 2009 dates want to be associate
with yellow. What about May 2008, 2007, 2010? Yellow also?
What colours do you want other months to be?

As a guess, in the code module of the sheet concerned:

Code
-------------------

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 Then
If Not Intersect(Target, Range("A2:A25")) Is Nothing Then
If IsDate(Target.Value) Then
Target.Offset(, 1).Interior.ColorIndex = Choose(Month(Target.Value), 3, 5, 7, 9, 6, 13, 21, 1, 8, 11, 4, 10)
Else
Target.Offset(, 1).Interior.ColorIndex = xlNone
End If
End If
End If
End Sub

-------------------

The cell will only change colour as you change/edit the individua
cells.
To establish the colours for a range of dates already in place, you ca
select those dates in column A and run this macro:

Code
-------------------

Sub blah()
For Each cll In Selection.Cells
If IsDate(cll.Value) Then
cll.Offset(, 1).Interior.ColorIndex = Choose(Month(cll.Value), 3, 5, 7, 9, 6, 13, 21, 1, 8, 11, 4, 10)
Else
cll.Offset(, 1).Interior.ColorIndex = xlNone
End If
Next cll
End Sub

-------------------
The cells in the next column to the right will be highlighted.
The colours are decided by the order of colour indices in the code:
3, 5, 7, 9, 6, 13, 21, 1, 8, 11, 4, 10
from Jan to Dec in that order. Adjust to suit (make them the same i
both macros)

 

[dave]

dave;578443 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?

dave

This is a bit confusing, so clarification required..
your subject title is 'Colors according to the month'
You cite dates 'between 1/1/09 to 31/01/08'; one date is in 2008 the
other in 2009 - is this intended?
From the next bit I can glean that May 2009 dates want to be associated
with yellow. What about May 2008, 2007, 2010? Yellow also?
What colours do you want other months to be?

As a guess, in the code module of the sheet concerned:

Code:
--------------------

  Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Cells.Count = 1 Then
  If Not Intersect(Target, Range("A2:A25")) Is Nothing Then
  If IsDate(Target.Value) Then
  Target.Offset(, 1).Interior.ColorIndex = Choose(Month(Target.Value), 3, 5, 7, 9, 6, 13, 21, 1, 8, 11, 4, 10)
  Else
  Target.Offset(, 1).Interior.ColorIndex = xlNone
  End If
  End If
  End If
  End Sub

--------------------

The cell will only change colour as you change/edit the individual
cells.
To establish the colours for a range of dates already in place, you can
select those dates in column A and run this macro:

Code:
--------------------

  Sub blah()
  For Each cll In Selection.Cells
  If IsDate(cll.Value) Then
  cll.Offset(, 1).Interior.ColorIndex = Choose(Month(cll.Value), 3, 5, 7, 9, 6, 13, 21, 1, 8, 11, 4, 10)
  Else
  cll.Offset(, 1).Interior.ColorIndex = xlNone
  End If
  Next cll
  End Sub

--------------------
The cells in the next column to the right will be highlighted.
The colours are decided by the order of colour indices in the code:
3, 5, 7, 9, 6, 13, 21, 1, 8, 11, 4, 10
from Jan to Dec in that order. Adjust to suit (make them the same in
both macros).

--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558
View this thread:http://www.thecodecage.com/forumz/showthread.php?t=159905

Microsoft Office Help

Hi,

thanks for get back to me. guess i've wrote the wrong date which
create confuses. actually what i want is if we enter date from
01/01/09 to 31/01/09 into any cell between A1:A100, the next cell
which will be between B1:B100 should coloured to yellow. if we enter
from 01/02/09 to 28/02/09 into any cell in A1:A100, the next cell
which will be B1:B100 should coloured blue and its should goes till
december. can you help me please?

 

[p45cal]

Choose(Month(Target.Value), 3, 5, 7, 9, 6, 13, 21, 1, 8, 11, 4, 10)
5, 7, 9, 6, 13, 21, 1, 8, 11, 4, 10)
Forums' (http://www.thecodecage.com/forumz/showthread.php?t=159905)

Hi,

thanks for get back to me. guess i've wrote the wrong date which
create confuses. actually what i want is if we enter date from
01/01/09 to 31/01/09 into any cell between A1:A100, the next cell
which will be between B1:B100 should coloured to yellow. if we enter
from 01/02/09 to 28/02/09 into any cell in A1:A100, the next cell
which will be B1:B100 should coloured blue and its should goes till
december. can you help me please?

Well, it seems I correctly guessed so I don't need to say any more. Did
you try it?