CODEWITHSUNDEEP
vbaexcel
FiddleDeDee
FiddleDeDee

Reputation: 183

Conditional Formatting cells surrounding a given value

So I've set up a conditional formatting VBA macro to highlight two cells: The one with the given string, and the one next to it.

The data set is:

A1          B1
------------------------
PluginID    NUM  
Host        ADDRESS  
Severity    High  
Port        PORT  
Description DESCRIPTION  
Solution    SOLUTION  
References  CVE

The VBA code is: 

    Sub High2()
'
' High2 Macro
'

'
    Columns("A:B").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND($B1=""High"",A1)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

This highlights the cell with 'High' in it, and the cell to the left, 'Severity'.

If I change the "=AND($B1=""High"",A1)" line to "=AND($B2=""High"",A1)" then excel will highlight the 2 cells above it in red instead, i.e. Host.

Can anyone help me with highlighting the 4 cells above and 8 cells below the string search-term as well (i.e. the Port, Description, Solution and References cells)?

Upvotes: 1

Views: 197

Answers (1)

Axel Richter
Axel Richter

Reputation: 61870

What you are actual doing if you "change the "=AND($B1=""High"",A1)" line to "=AND($B2=""High"",A1)"" is simply add a new rule. So this will be really the best approach. Adding as much rules as necessary.

Sub High2()
 With Columns("A:B").Cells
  .Range("A1").Activate
  .FormatConditions.Delete
  For i = 1 To 3 ' 3 above
   .FormatConditions.Add Type:=xlExpression, Formula1:="=($B" & i & "=""High"")"
   .FormatConditions(.FormatConditions.Count).SetFirstPriority
   With .FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 255
    .TintAndShade = 0
   End With
   .FormatConditions(1).StopIfTrue = False
  Next
  For i = 0 To 3 ' 4 below
   .FormatConditions.Add Type:=xlExpression, Formula1:="=($B" & .Rows.Count - i & "=""High"")"
   .FormatConditions(.FormatConditions.Count).SetFirstPriority
   With .FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 255
    .TintAndShade = 0
   End With
   .FormatConditions(1).StopIfTrue = False
  Next
 End With
End Sub

This could also be reached with only one rule:

=OR($B1048573:$B1048576="High", $B1:$B3="High")

But this will lead to bad performance since it is working as an array formula.

Upvotes: 1

Related Questions