Conditional Formatting using Date Comparison

1
votes

I am trying to create a conditional formatting rule in VBA that will change the cell background color only for the current date. I have a column of dates for the year. I am also using Office Excel 2010.

oSheet.Range(Cells(iStartRow, 1), Cells(iLastRow, 1)).FormatConditions.Add xlCellValue, xlEqual, "=" & Cells.Address & "=" & Date

I am trying to identify the current cell's address, so that it can be compared against today's date. Remember that this formula will be checked against a column of different dates.

I can create the conditional formatting in Excel easy enough, but I'm trying to recreate this statement in VBA code.

When I create the Conditional formatting necessary for the Dates Occurring in Excel and then view the formula in VBA; here are the results: =FLOOR(A32,1)=TODAY()

2
vbadateexcelconditional-formatting
You should be able to pull the current cell via: selection.address ... can you not? – Ditto

2 Answers

0
votes

The following will get you what you want:

oSheet.Range(Cells(iStartRow, 1), Cells(iLastRow, 1)).FormatConditions.Add Type:=xlExpression, Formula1:="=$A" & iStartRow & "=TODAY()"

If you want to reference a specific cell for your date comparison use (for example C1):

oSheet.Range(Cells(iStartRow, 1), Cells(iLastRow, 1)).FormatConditions.Add Type:=xlExpression, Formula1:="=$A" & iStartRow & "=$C$1"

Notice that in both examples there is a $ before the A because it is a fixed column, but there is NO $ before the row because the row (iStartRow) needs to be variable.

0
votes

I don't know how that CF rule's code fits into the surrounding code but you would do better to explicitly define the extents of the .Range as belonging to the same parent worksheet.

with oSheet
    .Range(.Cells(iStartRow, 1), .Cells(iLastRow, 1)).FormatConditions.Add Type:=xlExpression, Formula1:="=$A" & iStartRow & "=TODAY()"
end with

The prefix periods (e.g. full stop or .) tell .Range(... and .Cells(... that they belong to oSheet. Without them, your code may work or it may not. There have been several cases recently where a simple parent mis-definition (by relying on the ActiveSheet) broke otherwise good code.