Colors according to the month

  • Thread starter Thread starter dave
  • Start date Start date
D

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
 
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--
 
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 -
Click to expand...

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
 
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
Click to expand...

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
Click to expand...

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?
 
Back
Top