VBA Conditional Formatting - Change Date Font color in column J if value in column K >1

[SilverTRader]

Sheet 1 is created with VBA code and populated with data starting at Row 6 from an Access recordset object.
Row J is Requested Date. It can be null or the latest date.
Row K is the number of Requested Dates - it can be Null (if the date is empty), 1 or many
In the case the number in K > 1 - We want the format of the Date to be changed. This way the report shows more than one date exist.

Example of the Objective
Date .......Number
1/1/2013 ..1
1/1/2013 ..3 ' Greater than 1 - formated Date fonts
....................' both blank
1/1/2013 ..1

' Runs but doesn't provide the intended results
Sub Macro6()
With Sheets(1)
        Application.Goto .Range("J6")
With .Range("J6:J" & .Range("A" & .Rows.Count).End(xlUp).Row)
            .FormatConditions.Delete
            .FormatConditions.Add Type:=xlExpression, Formula1:="=ActiveCell.Offset(-1, 0) > 2"
    With Selection.FormatConditions(1).Font
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = -0.249946592608417
    End With
            .FormatConditions(1).StopIfTrue = True
        End With
End With
End Sub

My current way to do this is with:

' introwpos is Row 6  - intMaxRecordCount is the number of records from MSAccess
1590        With ObjXL.ActiveWorkbook.ActiveSheet
1600            For i = intRowPos To intMaxRecordCount + intRowPos
1610             If .Cells(i, "B").value <> .Cells(i - 1, "B").value Then
1620                   .Range(.Cells(i, "B"), .Cells(i, "B")).Font.FontStyle = "Bold"
                       '.Cells(i, 33).Value = .Cells(i, 3).Value
1630             Else
                       'Another format option
1640             End If
1650           Next i

The problem with the last code (that works) is when the end Excel user wants to do a "what if" and change a number from 1 to 2 -
the date won't reformat. The question code above would allow the end Excel user to see the changes update.

 

[Andrew Poulsom]

Is it?

.FormatConditions.Add Type:=xlExpression, Formula1:="=K6>2"

 

[SilverTRader]

I am so HAPPY to report it works perfectlly!
My bad in the question. Was suppose to be greater than 1 (not 2)
I thought this would all just point to K6, not the relative position.

What threw me off was the Conditional Formatting Rules Manager for a given cell shows
Formula: =K6>1 (the format) .. =$J$6:$J$7741

Sub Macro6()
With Sheets(1)
        Application.Goto .Range("J6")
With .Range("J6:J" & .Range("A" & .Rows.Count).End(xlUp).Row)
            .FormatConditions.Delete
            .FormatConditions.Add Type:=xlExpression, Formula1:="=K6>1"
            '.FormatConditions.Add Type:=xlExpression, Formula1:="=ActiveCell.Offset(-1, 0) > 2"
    With Selection.FormatConditions(1).Font
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = -0.249946592608417
    End With
            .FormatConditions(1).StopIfTrue = True
        End With
End With
End Sub

Thanks so much!

 

[Andrew Poulsom]

The Formula1 property expects a valid formula. It won't translate VBA, so this will literally be the formula:

=ActiveCell.Offset(-1, 0) > 2

In this formula:

=K6>1

both the column and the row reference are relative, so the row reference will increment as the row in the range that the formula applies to increments. In other words, in K7 it will be:

=K7>1

 

[SilverTRader]

I had the quotes in, removed them as you suggested and it doesn't work the same.
Now, after changing to the =ActiveCell.Offset(-1,0)>1
The using the Excel Conditional Formatting - manage rule - the cell still shows =$H$6:$H$42189
The data starts at H6 and goes to the number of records returned from Access (actually SQL Server DB).
But, all fonts in the column change to the color. The Manage Rule shows the result is TRUE.
This means that if I manually change the K7 from 1 to 0 or 0 to 1 - there is not any update.
Excel 2010
So, the first formula in quotes works correctly. It also allows the user to adjust the numbers e.g. K7 and let the formual individually adjust.
Here is the code to show the difference better. My report has six different columns with using this wonderful formula. This runs in Access VBA Automation for Excel so very fast!! It is wonderful.

With ObjXL ' conditional format for J using K    ObjXL is a variable in Access 2010 VBA   -     Dim ObjXL                    As Excel.Application
    'With .Sheets(1)
        .Goto .Range("J6")
    'End With
    With .Range("J6:J" & .Range("A" & .Rows.count).End(xlUp).ROW)
                .FormatConditions.Delete
                .FormatConditions.Add Type:=xlExpression, Formula1:="=K6>1" ' works Great!!!
                '.FormatConditions.Add Type:=xlExpression, Formula1:=ActiveCell.Offset(-1, 0) > 1 ' doesn't work the same
        With ObjXL.Selection.FormatConditions(1).Font
                .Color = 12611584
                .TintAndShade = 0 ' combination of themeColor and Tint is BLUE
        End With
                .FormatConditions(1).StopIfTrue = True
    End With
End With

 

[Andrew Poulsom]

Is your problem solved?

 

[SilverTRader]

Absolutely solved! Thanks very much. Is there a plae on this forum for me to mark it as solved?

 

[Andrew Poulsom]

No, it's OK, I just wasn't sure from what you wrote.