Tech Support Guy banner
Status
Not open for further replies.

Conditional Formatting Excel 2010

2K views 15 replies 3 participants last post by  beeky 
#1 ·
The old versions of Excel were easier than this version 2010. I am trying to either code or conditionally format a column whereby if the date is:

< Today's Date = Red
> Today's Date = Green
= Today's Date = Amber

The column where the dates are is Column H and the dates start on row 2.

I have also tried to insert a module with a case statement thus:

Sub Macro1()
Dim i As Integer

i = 2
Do While Range("H" & i).Value <> ""
Select Case CDate(Range("H" & i).Value)
Case Date 'Todays Date
Range("H" & i).Font.Color = vbRed
Case Is < Date 'previous Dates
Range("H" & i).Font.Color = vbBlue
Case Else 'Future Dates
Range("H" & i).Font.Color = vbGreen
End Select

i = i + 1
Loop
End Sub

but to no avail the workbook tells me I will lose the macro if saved in this format (i.e. xlsx) which should be the correct format.

I have tried and tried but to no avail - can anyone help me please?
 
See less See more
#2 ·
macros in Excel 2010, are saved in a new macro enabled workbook format (.xlsm) to save your workbook or the macro won't be saved.

if you use conditional format
i would select the column H
and then add three rules as you have described

i would use the formula and add the cell

H1 < Today() format Red
H1 > Today() format Green
H1 = Today() format Amber

works for me OK
 
#4 ·
Thank you ETAF and Garf

I have clearly got myself into a muddle. Try as I might I cannot get the hand of using the conditional formatting function in Excel 2010. In the older versions you could simply add a formula as you indicated above and then add a new one and so on. With this new version you get all this minimum, midpoint, highest and then type lowest, percentile, highest etc. Very confusing for a small brain like mine. I simple want to add a simple formula that says: if H3 (and then obviously down the column) is greater than today make it Red, if it is less than today make it Green or if it is today make it amber. My problem is that I have no idea where to put the formula in this new version of conditional formatting. Any help would be appreciated.
 
#6 ·
Thank you very much this now seems to work. One last thing I have now formated the whole column using the above code but I don't want any cell (including the heading) to use the conditional formatting - inother words I want text as Black or automatic. How can I do this please.
 
#7 ·
when you click OK - you can show the range for the condition to apply to

or
before you add the cond format - select the range you want to apply and remove ANY $ signs for cells that you want to change as the cell changes -if that makes sense
 
#8 ·
Hi, Yes I understand what your saying but since I have no control on the number of rows that will ultimately be completed I needed to highlight the whole column. When the formula for each condition is completed it looks like $H:$H in the 'Applies to' range. I have tried to amend the $H:$H to $H3:$H and so on but without luck. I was hoping there could be a way of saying - If H1 = (Text) format Black, or something similar. Do you have any ideas about how I could achieve this?
 
#10 ·
Would this be to each of the conditional formulas already there or a new condition where only the heading cell is highlighted? I have provisionally tried these and it does not seem to work. It will be something I have done wrong but I am not sure what. Thank you for your patience.
 
#15 ·
Thanks Garf. I did think about that but as you rightly say the other option is neater. I would still like to know - for interest really - how to achieve the same result by incorporating the Case Statement I showed at the start of this thread. I got to the stage on putting the code into a macro and then into a new module using the VBA editor but I when I opened the spreadsheet I had to manually run the macro rather than the code automatically updating new data. For those who don't want to go back to the beginning of the thread my Case statement was:

Sub Macro1()
Dim i As Integer

i = 2
Do While Range("H" & i).Value <> ""
Select Case CDate(Range("H" & i).Value)
Case Date 'Todays Date
Range("H" & i).Font.Color = vbRed
Case Is < Date 'previous Dates
Range("H" & i).Font.Color = vbBlue
Case Else 'Future Dates
Range("H" & i).Font.Color = vbGreen
End Select

i = i + 1
Loop
End Sub

Cheers
 
#16 ·
Thanks Garf. I did think about that but as you rightly say the other option is neater. I would still like to know - for interest really - how to achieve the same result by incorporating the Case Statement I showed at the start of this thread. I got to the stage on putting the code into a macro and then into a new module using the VBA editor but I when I opened the spreadsheet I had to manually run the macro rather than the code automatically updating new data. For those who don't want to go back to the beginning of the thread my Case statement was:

Sub Macro1()
Dim i As Integer

i = 2
Do While Range("H" & i).Value <> ""
Select Case CDate(Range("H" & i).Value)
Case Date 'Todays Date
Range("H" & i).Font.Color = vbRed
Case Is < Date 'previous Dates
Range("H" & i).Font.Color = vbBlue
Case Else 'Future Dates
Range("H" & i).Font.Color = vbGreen
End Select

i = i + 1
Loop
End Sub

Cheers
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top