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

 

[CreativeUsername]

Ah... why didn't I think of that. BUT it doesn't work. it changes the data in the offset field to dates and loses the color format.

think I need an If statement based loop. Something like With the off set if active cell condition is true... make the offset red.

 

[AlphaFrog]

I don't see how it could change data to dates. There is nothing in the whole code that changes data. Did you make any changes to my part of the code, or do you have additional code?

 

[CreativeUsername]

Ok... O copied the above code to a new module and ran that. It doesn't change things to dates. I'm guessing a previous operation over selected the range that was converted to date range.

BUT nothing happens.

the values are not red as corresponds to the cell 24 to the left.
I played with a few modifications and versions of it so possibly I broke it prior to my earlier post.

I have made SOME progress with the code below but It only fills the whole filed a uniform color. FIRST bright green which was not on the referenced range (????) then if I play with the offset one to the right or left the code generates an error message.

Sub PHPColor()

 Dim numrows As Long
    
    Range("AD2").Select
    
    numrows = Range("AD2", Range("AD2").End(xlDown)).Rows.Count
    
    With Range("AD2", Range("AD2").End(xlToRight)).Resize(numrows)
        .Interior.Color = ActiveCell.Offset(0, -24)
    
      
    End With
    
End Sub

[CODE]

Am I thinking about this wrong?  perhaps there is another way to get the same result.
I have a date that I create 24 dates from counting backward one month at a time.  Any of those dates that fall in the referenced time period are important.   Then further to the right I  have the parsed 24 character alpha numeric field.  the most important thing is that the single character alpha numeric cells are colored.   Or marked in some way so that I can then filter for only the ones that are "bad".

 

[CreativeUsername]

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

is close... but it only works for the first cell and the rules don't show up in the conditional format dialogue box (which is fine).

 

[AlphaFrog]

Whoa. Slow down a bit. You're jumping araound a lot with code changes and results.
Lets figure out what's going on with the code from post #10 . It does do something. So lets figure out what's going on. It may just need a tweak. No need to scrap it and write a completely new procedure.

Ok... O copied the above code to a new module and ran that. It doesn't change things to dates. I'm guessing a previous operation over selected the range that was converted to date range.

BUT nothing happens.

the values are not red as corresponds to the cell 24 to the left.
I played with a few modifications and versions of it so possibly I broke it prior to my earlier post.

Something does happen even if the cells are not colored correctly.

One point of confusion is in post #9 you say you want the same Conditional Format for cells offset 25 to the right. The Cell that is offset 25 to the right of F2 is AE2. Then you talk about AC2 and then later in other posts you reference AD2. So what is the exact range you want to format?

I understand now you want to format the cells to the right based on the values in cells F2 (and surrounding). The code on post #10 doesn't do that. I'll change it when you tell me the correct offset from F2; is it AC2, AD2, or AE2?

 

[AlphaFrog]

Try this. The remaining uncertainty, as discussed above, is the Offset from F2. The code below offsets 23 columns from cell F2 to AC2. Change the 23 to suit.

[color=darkblue]Sub[/color] Conditions()
    
    [color=darkblue]Dim[/color] numrows [color=darkblue]As[/color] [color=darkblue]Long[/color], col [color=darkblue]As[/color] Range, ColumnOffSet [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    ColumnOffSet = [COLOR=#ff0000]23[/COLOR]   [color=green]'column AC (offset from column F)[/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]
        
        Range("F2").Offset(, ColumnOffSet).Select
        [color=darkblue]With[/color] .Offset(0, ColumnOffSet)
            .FormatConditions.Delete
            [color=darkblue]With[/color] .FormatConditions.Add(Type:=xlExpression, Formula1:="=AND(F2>=$A2,F2<=$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]
    
    Range("F2").Select
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[CreativeUsername]

I'll change it when you tell me the correct offset from F2; is it AC2, AD2, or AE2?

Sorry. The range F2 to AC2 to XLDOWN works p0erfectly. I only need to copy the colors from that range to AD2 to BA2 and XL Down.

Thanks for the help.

 

[CreativeUsername]

This works perfectly... thank you.

 

[CreativeUsername]

Question: Is it possible to modify the condition code to include that the dates and offsets are only red if the dates in F fall between the dates in column A and B. AND if the character in the 24 unit offset to the right Is NOT a "0"?

What I made gets there by filtering and removing those items, and copying the result to new report tabs. BUT there is a desire to see the conditional format NOT color items that should not be red.

Above it does in that a date can fall between the A and B dates BUT it can have a character in the offset cell that is "0". If that is the case it should NOT be red but is red.