VBA Conditional Formatting Formula Simple Error

randomwit

New Member
Joined
Apr 2, 2010
Messages
16
I am having problems with my code snippet below. I am almost certain that the error lies in Formula1:=CStr(ucl.Address).

I can get the code to work by changing it to ucl.Value, but I need the formatting to be linked to the ucl CELL, not it's value, so that it will update when the value in ucl changes...

I feel like i've tried most of the things I can think of, but no luck.

How do I get this to work the way I described above?

Help, please :)

Code:
Sub AddConditionalFormats(FormatArea As Range)
    
    Dim col As Range
    Dim ucl As Range
    Dim lcl As Range
    
    FormatArea.FormatConditions.Delete
       
    For Each col In FormatArea.Columns
        
        Set ucl = Cells(4, col.Column)
        Set lcl = Cells(2, col.Column)
        
        col.Select
        
        'GREATER THAN UCL
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:=CStr(ucl.Address)
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
...
...
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,358
Try something like this...

Code:
With col
    'GREATER THAN UCL
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:=[COLOR="Red"]ucl
    .FormatConditions(.FormatConditions.Count).Interior.ColorIndex = 38[/COLOR]
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
End With

This sets the CF format to a red backgroud. No need for col.Select
 
Last edited:

randomwit

New Member
Joined
Apr 2, 2010
Messages
16
That helped clean up the code a bit, but it does the same thing as applying the condition to a hard value (or ucl.value). Since the cells ucl and lcl are subject to change values, i need the formatting to adjust.

For example, say one data point in column C is higher than UCL when code executes. The cell text should be red (this code will do that). However, if UCL changes, such that the red cell is no longer greater, it should not have the formatting anymore (this code cannot do that).

If i apply conditional formatting manually, it will do this, but I cannot emulate it using vba.

Code:
Sub AddConditionalFormats(FormatArea As Range)
    
    Dim col As Range
    Dim ucl As Range
    Dim lcl As Range
    
    Application.Volatile
    FormatArea.FormatConditions.Delete
       
    For Each col In FormatArea.Columns
        
        Set ucl = Cells(4, col.Column)
        Set lcl = Cells(2, col.Column)
        
        With col
            'GREATER THAN UCL = RED TEXT
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:=ucl
            .FormatConditions(.FormatConditions.Count).Font.Color = -16383844
            .FormatConditions(.FormatConditions.Count).Font.TintAndShade = 0
            .FormatConditions(.FormatConditions.Count).StopIfTrue = False
        End With
        
        With col
            'LESS THAN LCL = BLUE TEXT
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:=lcl
            .FormatConditions(.FormatConditions.Count).Font.Color = -4165632
            .FormatConditions(.FormatConditions.Count).Font.TintAndShade = 0
            .FormatConditions(.FormatConditions.Count).StopIfTrue = False
        End With
        
    Next col
              
End Sub
 

randomwit

New Member
Joined
Apr 2, 2010
Messages
16
For what it's worth, the answer is a little tricky. Finally got it, though.

Formula1:=("=" & ucl.Address)

will link the formatting to the cell, not the value.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,852
Messages
5,574,660
Members
412,608
Latest member
Guromir
Top