CODEWITHSUNDEEP
vbaexcelconditional-formatting
Kristina
Kristina

Reputation: 699

Conditional formatting on non-empty cells - Excel VBA 2010

I have 3 correlation matrices:

Input Correlation:

                 Acc & Health     AE     Avemco Aviation      Bail
Acc & Health         1           0.15        0.15            0.15
AE                  0.15          1          0.15            0.25
Avemco Aviation     0.15         0.15         1              0.15
Bail                0.15         0.25        0.15              1

Output Correlation:

                Acc & Health      AE     Avemco Aviation      Bail
Acc & Health         1          
AE                 0.158          1     
Avemco Aviation    0.139        0.158            1  
Bail               0.177        0.258          0.130           1

Comparison Correlation (absolute value of Input - Output Correlation tables):

                Acc & Health      AE     Avemco Aviation      Bail
Acc & Health       0.000             
AE                 0.008        0.000       
Avemco Aviation    0.011        0.008          0.000    
Bail               0.027        0.008          0.020         0.000

I'm applying conditional formatting to the Comparison Correlation table if the Input Correlation table values are greater than 0.2 AND another mathematical condition AND if the cells in the Comparison Correlation table are NOT blank:

HOWEVER, I'm still getting formatting in blank cells like E3 (AE and Bail) since the input value is greater than 0.2. How do I fix this so no formatting is done on blank cells??

Part of my code in VBA:

.Range(.Cells(5, 2), .Cells(LastRowAgg, LastColumnAgg)).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
                "=AND((('Input Correlation'!B5:AT49-'Aggregate Loss Correlation'!B5:AT49)/'Input Correlation'!B5:AT49)>=(1/3),'Input Correlation'!B5:AT49>=0.2,B5:AT49<>"" "")"
            Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
            With Selection.FormatConditions(1).Interior
                .PatternColorIndex = xlAutomatic
                .Color = 255
                .TintAndShade = 0
            End With
        Selection.FormatConditions(1).StopIfTrue = False

Any help will be appreciated!

Upvotes: 1

Views: 1774

Answers (1)

Achaibou Karim
Achaibou Karim

Reputation: 653

I suggest you use an if statement together with the isblank() function:

... Formula1:="=IF(isblank(a32);;and(a32<=a$19;a32>a$18))"

If isblank is True, do nothing else evaluate formula (and(a32<= ... )

Upvotes: 1

Related Questions