VBA for dynamic conditional formatting.

[CreativeUsername]

Hi,

I need to reference a cell in Column A and B on the Active row for a conditional formatting command and am having trouble.
It isn't the most glorious bit of code but it works with the exception that its hard coded to reference A2 and B2. Rather I need A and ActiveCell.row or ActiveCell.Offset. I'm not hitting it right with the syntax apparently and I don't find an example online to emulate.

My code is below. It selects a horizontal range of dates and colors any that fall between the date range in formula section.

What am I doing wrong?

Sub Conditions()
'
' Conditional format   
    
    Dim x As Integer
    Application.ScreenUpdating = False
    NumRows = Range("F2", Range("F2").End(xlDown)).Rows.Count
    
    'Range("F2").Select
    'Range(("F2"), Selection.End(xlToRight)).Select
       
    
    Range("F2").Select
    Range(("F2"), Selection.End(xlToRight)).Select '<------------Start row segment selection here.
    
For x = 1 To NumRows
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=$A$2", Formula2:="=$B$2"
        'Formula1:=ActiveCell.Offset(0,5), Formula2:=ActiveCell.Offset(0,4) '<---- offset from active cell
        
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
        ActiveCell.Offset(1, 0).Select
        Range(ActiveCell, ActiveCell.End(xlToRight)).Select
    Next
    Application.ScreenUpdating = True
End Sub

This COULD be structured to check each cell in the range F2 to AC2 to the bottom (xldown) BUT the items is 27K rows long... so I prefer to minimize cell by cell manipulations.

thanks

 

[Fluff]

How about

Formula1:=ActiveCell.Offset(0, -5).Address, Formula2:=ActiveCell.Offset(0, -4).Address '<---- offset from active cell

 

[CreativeUsername]

How about

Formula1:=ActiveCell.Offset(0, -5).Address, Formula2:=ActiveCell.Offset(0, -4).Address '<---- offset from active cell

No version of that appears to work. The macro I recorded created

 Formula1:="=$A$2", Formula2:="=$B$2" [code]  

I need that to be dynamic to the cell on the active row but same column.

I think the issue is because it happens inside a conditional format.

 

[AlphaFrog]

You can apply the conditional formatting to the entire range at one time. Excel will automatically adjust the relative references in the formula.

[COLOR=darkblue]Sub[/COLOR] Conditions()
    
    [COLOR=darkblue]Dim[/COLOR] numrows [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    Range("F2").Select
    
    numrows = Range("F2", Range("F2").End(xlDown)).Rows.Count
    
    [B][COLOR=darkblue]With[/COLOR] Range("F2", Range("F2").End(xlToRight)).Resize(numrows)[/B]
        [COLOR=darkblue]With[/COLOR] .FormatConditions.Add(Type:=xlCellValue, Operator:=xlBetween, Formula1:="=[B]$A2[/B]", Formula2:="=[B]$B2[/B]")
            .SetFirstPriority
            [COLOR=darkblue]With[/COLOR] .Font
                .Color = -16383844
                .TintAndShade = 0
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
            [COLOR=darkblue]With[/COLOR] .Interior
                .PatternColorIndex = xlAutomatic
                .Color = 13551615
                .TintAndShade = 0
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
            .StopIfTrue = [COLOR=darkblue]False[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[Fluff]

How about

Formula1:="=" & ActiveCell.Offset(0, -5).Address, Formula2:="=" & ActiveCell.Offset(0, -4).Address

 

[CreativeUsername]

AlphaFrog - the above seems to have worked. I'm not sure I see what made it work? What does the resize numrows do?

 

[AlphaFrog]

AlphaFrog - the above seems to have worked. I'm not sure I see what made it work? What does the resize numrows do?

Numrows is the number of rows in your dynamic range.
numrows = Range("F2", Range("F2").End(xlDown)).Rows.Count

The red part below references the top row. Then the resize part resizes that top row to include all the rows in the range based on numrows.
With Range("F2", Range("F2").End(xlToRight)).Resize(numrows)

What made it apply the CF to every row was changing this absolute row reference...
Formula1:="=$A$2", Formula2:="=$B$2"
To this relative row reference.
Formula1:="=$A2", Formula2:="=$B2"

With the relative row reference, Excel will automatically adjust the formulas for each row.


Relative and Absolute References

 

[CreativeUsername]

Thank you. I was playing with the "$" with that expectation but getting odd results. Great to know it works like this in VBA.


Now I need to take a duplicate range to the right AD2.end(xlToRight) and XlDown and make the cell colors and fonts match cell for cell with the cell 24 places to its left on the same row.

I thought it would be easier to modify or hijack the above.

 

[CreativeUsername]

Because its conditional formatting this is more difficult than I thought it would be. Is there a way to make this color its own cell AND the cell 25 spaces to the right the same?

or can it be duplicated to change a cell like AC2 red if F2 is between the dates on A and B?

 

[AlphaFrog]

[color=darkblue]Sub[/color] Conditions()
    
    [color=darkblue]Dim[/color] numrows [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    numrows = Range("F2", Range("F2").End(xlDown)).Rows.Count
    
    Range("F2").Select
    
    [color=darkblue]With[/color] Range("F2", Range("F2").End(xlToRight)).Resize(numrows)
        .FormatConditions.Delete
        [color=darkblue]With[/color] .FormatConditions.Add(Type:=xlCellValue, Operator:=xlBetween, Formula1:="=$A2", Formula2:="=$B2")
            .Font.Color = -16383844
            .Interior.PatternColorIndex = xlAutomatic
            .Interior.Color = 13551615
        [color=darkblue]End[/color] [color=darkblue]With[/color]
        
        [color=darkblue]With[/color] .Offset(0, 25)
            .FormatConditions.Delete
            [color=darkblue]With[/color] .FormatConditions.Add(Type:=xlCellValue, Operator:=xlBetween, Formula1:="=$A2", Formula2:="=$B2")
                .Font.Color = -16383844
                .Interior.PatternColorIndex = xlAutomatic
                .Interior.Color = 13551615
            [color=darkblue]End[/color] [color=darkblue]With[/color]
        [color=darkblue]End[/color] [color=darkblue]With[/color]
        
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]