Conditional Statements using VBA in Excel

[mondeoman]

I am using Excel 2010. I have a column with dates and I want the dates (or the background cell color)to change based on whether the date is greater than today, less than today or today. I have had a feeble attempt at changing the color using this formula: =IF(H3>TODAY(),"Red",IF(H3<TODAY(),"Green","Amber") but this is just returning the text version and obviously in a different cell to that which I want to highlight. I thought I could use a case statement but I don't know how to incorporate one in the spreadsheet. Can anyone help please.

 

[PHV]

Use conditional Formating, NO VBA required.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

I would go with PHV's suggestion with conditional Formatting,

but if you have your heart set on VBA, here it is (if your data starts in row 2 and your column H is a Date):

Dim i As Integer

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

    i = i + 1
Loop

Have fun.

---- Andy

 

[mondeoman]

To PHV and Andrzejek thank you both for your inputs.

PHV
I would like to have used conditional formatting but I could see now way of doing it with date formats. Perhaps PHV you could help or point me to a good tutorial.

Andrzejek
Having said that I would dearly like to learn how to incorporate the select case into a workbook as part of my ongoing understanding of VBA. I am using a macbook and have gone to tool, macro, Visual Basic Editor. Selected a new module and inserted the code you kindly provided. It seems I have to name the function with something like:

public function "FunctionName (param1 As type) As ??????

End Function

but I don't know what should be in the gaps or how to call this once the workbook is open.

I would really like to get my head round this so I would be very grateful for your further help/advice.

 

[Andrzejek]

The easiest way to learn VBA in Excel (IMHO) is to record a macro in Excel and see what it does in code (Alt-F11)

So what you can do is: start recording any simple macro, click on any cell, stop recording. Go to VBA editor and find what you just recorded. You may have something like this:

Option Explicit

Sub Macro1()[green]
'
' Macro1 Macro
'

'[/green]
    Range("D7").Select
End Sub

Replace the code inside the macro ([tt]Range("D7").Select[/tt]) with my code.

Or, in this new module do this:

Sub This_Is_My_New_Macro()
[green]
'Paste the code here
[/green]
End Sub

Have fun.

---- Andy

 

[mondeoman]

Thank you Andy

I have done exactly as you said. I started a new macro, then stopped it. I went to the VBA window and in module1 this code was there

'Sub Macro1()
'
' Macro1 Macro
'

'
End Sub'

I replaced this with your code so it looked like this:

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

I went to save the worksheet and got a message saying that 'Visual Basic Macros will be removed if you save the file in this format. Are you sure etc. The choose a different format'

Now I am using Excel 2010 (MAC format) with .xlxs as the normal file format. Where am I going wrong?

 

[Andrzejek]

When you do Save:

Save as Type: (choose =>) Excel Macro-Enabled Workbook (*.xlsm)

But the question here is: did my code run/work for you?

Have fun.

---- Andy

 

[mondeoman]

Hi Andy

Many thanks for your help so far. I really appreciate it.

Right I have had a new look at the problem. The first thing I noticed was that I gave you some wrong information as the first row for dates should have been 3 not 2 so I have changed that. However, when I open the spreadsheet and insert a new date I have to physically run the macro (kept default name macro1) before the changes take place.
In the project window the macro comes under Modules Module1 but the sub refers to macro1. Here is the code as it appears in the VB Editor. Is there something I should be doing to ensure the macro automatically fires up when the workbook opens?

Sub Macro1()
Dim i As Integer

i = 3
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

I have attached a screendump (held in box.net) of the VB Editor and project window.

 

[SkipVought]

when I ... insert a new date I have to physically run the macro

Is there something I should be doing to ensure the macro automatically fires up when the workbook opens?

Not sure WHEN you want this procedure to "automatically" run???

There are events, such as the Workbook_Open event and the Worksheet_Change event, from which your procedure can be called. First, second or both?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Andrzejek]

Skip, a question:

If mondeoman would use Conditional Formatting (instead of the 'code' approach), would he get the data to change color "automatically" after any change to the data?

Just curious….


Have fun.

---- Andy

 

[mondeoman]

Hi Andy

Yes I accept that conditional formatting is an easier way of doing things and I have managed to get the colours to change based on the date. However, as I want the conditions to apply to the whole column (i.e. I have no idea at this stage how many rows will be used) I have found that where cells have just text the font colour is also changed which I don't want. So for example in my case Column 'H' is the column where the color of the font should change depending on whether the date is today, before today or after today. I have used the formulas:

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

in the conditional format options where the range applies to $H:$H

However, the heading (Review Date) in column 'H' cell H1 also changes colour to green where it was originally black.

Second, I believe in the long term knowledge of using VBA will be useful in terms of flexibility and I would have loved to see the coding option produce the results I wanted. I accept I don't know enough about it aas yet but I thought this would have been a fairly basic place to start. If someone could let me know how to make the code work I would be grateful.

 

[SkipVought]

If mondeoman would use Conditional Formatting (instead of the 'code' approach), would he get the data to change color "automatically" after any change to the data?

YES!!!!!

It's a no-brainer!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

However, as I want the conditions to apply to the whole column (i.e. I have no idea at this stage how many rows will be used

If you use Excel as designed, then that would not be an issue. The Structured Table feature (Insert > tables > Table) redefines the table range as rows are added and removed from the table, as well as LOTS of other features that have become an essential part of my toolbox.

Yes, learn and use VBA for the things that Excel cannot do automatically. But THIS is not one of them!

I have found that where cells have just text the font colour is also changed

What is that supposed to mean? Are you saying that you have BOTH date values and TEXT in the same column? That is a recipe for disaster in a table: mixing text and numbers is not a best and accepted practice, although Excel lets you shoot yourself in the foot anytime you like.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mondeoman]

No I am sorry I have clearly not explained myself well. This is a very straightforward worksheet where a task is assigned to someone and a review date for the completion of the task is agreed. So that it is easy to see whether a task is coming up for review or has past the review date etc. The date should change color simply on the basis of whether it is before today or after today or is indeed today. At the top of the column are headings - in this columns case the heading is 'Review Date'. Since I don't know how many tasks over a period of time will be reviewed I have applied the conditional formatting (using the built in function) to the whole column. In doing this for some reason the Heading 'Review Date' has turned green which I don't want it to do as it is then out of sink with the black text of the remaining headings. I simply wanted to know if there was anyway of excluding the heading (using, if you like, the inbuilt conditional formatting function) from the other conditional formats.

Using the Case statement was really a way of helping me learn how to do it that way. Sorry if I confused everyone.

 

[SkipVought]

Since I don't know how many tasks over a period of time will be reviewed I have applied the conditional formatting (using the built in function) to the whole column.

No you don't.......IF you use the Structured Table feature in Excel 2007+.

Table headings ought NOT to be part of CF under the stated circumstances. Your criterial is solely based on DATES which are NUMERIC values.

Also, good spreadsheet structure and design prohibit PRE-formatting or PRE-formula loading. It is both unnecessary and created other problems. Hence your problem, as quoted directly above.

Structured Tables -- step up to it.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mondeoman]

Thank you for your help. I have found a way of excluding the heading cell by using the ISNUMERIC function [e.g. AND(H1 < Today(), ISNUMBER(H1) )]. Anyway I appreciate your help so many thanks for your patience.