Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 6th 06, 06:24 AM posted to microsoft.public.excel.misc
andy75
 
Posts: n/a
Default How can i change cell colour depending on month of date in cell?


I have a column with a range of dates corresponding to when an item was
tested and would like to automatically change the colour depending on
which month it was tested. Jan-Mar = red, Apr-Jun= green, Jul-Sep=
blue and Oct-Dec= yellow. No date = no colour. Anyone have any
suggestions? Do I need to run a macro or is there a simpler way?

Thanks

Andy


--
andy75
------------------------------------------------------------------------
andy75's Profile: http://www.excelforum.com/member.php...o&userid=30177
View this thread: http://www.excelforum.com/showthread...hreadid=498604


  #2   Report Post  
Old January 6th 06, 07:45 AM posted to microsoft.public.excel.misc
SludgeQuake
 
Posts: n/a
Default How can i change cell colour depending on month of date in cell?

Here's a bit of code that might work (here, I have all dates in column A):

Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next

Dim i As Integer
Dim iColor As Integer

i = 0
iColor = 0

i = Month(Target.Value)

If Not Intersect(Target, Range("a:a")) Is Nothing Then
Select Case i
Case 1 To 3
iColor = 3
Case 4 To 6
iColor = 10
Case 7 To 9
iColor = 11
Case 10 To 12
iColor = 6
Case Else
End Select

Target.Font.ColorIndex = iColor

End If
End Sub

"andy75" wrote:


I have a column with a range of dates corresponding to when an item was
tested and would like to automatically change the colour depending on
which month it was tested. Jan-Mar = red, Apr-Jun= green, Jul-Sep=
blue and Oct-Dec= yellow. No date = no colour. Anyone have any
suggestions? Do I need to run a macro or is there a simpler way?

Thanks

Andy


--
andy75
------------------------------------------------------------------------
andy75's Profile: http://www.excelforum.com/member.php...o&userid=30177
View this thread: http://www.excelforum.com/showthread...hreadid=498604


  #3   Report Post  
Old January 6th 06, 07:46 AM posted to microsoft.public.excel.misc
Biff
 
Posts: n/a
Default How can i change cell colour depending on month of date in cell?

Hi!

If you want to change the cell *background color* then you will need an
event macro to do this. You can use conditional formatting (no VBA code
required) for up to 3 conditions but if you want the *background color* to
change then you have 4 conditions.

If you want the *text color* to change then you can use cf. You still have 4
conditions but you can set one of those to be the default leaving you with
the 3 to conditionally format.

Assume your dates are in the range A1:A10.
Set the default font color for that range to RED.

Now, apply conditional formatting:

Select the range A1:A10
Goto FormatConditional Formatting
Condition 1
Formula is: =AND(MONTH(A1)=4,MONTH(A1)<=6)
Click the Format button
Set the font color to GREEN
OK
Click the Add button
Condition 2
Formula is: =AND(MONTH(A1)=7,MONTH(A1)<=9)
Click the Format button
Set the font color to BLUE
OK
Click the Add button
Condition 3
Formula is: =AND(MONTH(A1)=10,MONTH(A1)<=12)
Click the Format button
Set the font color to YELLOW
OK out

Now, as you type dates in the range they will appear red but as soon as you
hit the enter key they will change to the appropriate color.

Note: that YELLOW font is pretty hard to see on a white background! Maybe
you can experiement with your color choices even applying bolding if
desired.

Biff

"andy75" wrote in
message ...

I have a column with a range of dates corresponding to when an item was
tested and would like to automatically change the colour depending on
which month it was tested. Jan-Mar = red, Apr-Jun= green, Jul-Sep=
blue and Oct-Dec= yellow. No date = no colour. Anyone have any
suggestions? Do I need to run a macro or is there a simpler way?

Thanks

Andy


--
andy75
------------------------------------------------------------------------
andy75's Profile:
http://www.excelforum.com/member.php...o&userid=30177
View this thread: http://www.excelforum.com/showthread...hreadid=498604





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I create a schedule from a list of dates ? Gavin Morris Charts and Charting in Excel 2 October 28th 10 04:08 PM
Change colour of Text if number in cell exceed limit Lewis Koh Excel Worksheet Functions 2 August 2nd 05 02:16 AM
Cell Change Color - Need Help alani New Users to Excel 3 June 29th 05 03:50 PM
COLOUR CHANGE IS A CELL Mav Excel Discussion (Misc queries) 1 April 23rd 05 11:22 AM
how do i get cell to change colour when text is added to it to hi. Mav Excel Discussion (Misc queries) 2 April 23rd 05 10:30 AM


All times are GMT +1. The time now is 04:59 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017