1

My script works in its entirety but there's one section of it I want to "tone" up and simplify. I have three values that I'm removing highlight from if one of the cells meets a certain condition. I apply the same WITH statement to all three cells. It'd be nice to trim it to select all three values so I can reduce it to only one loop.

I'm including only the important part of the script for simplicity so ignore the missing definitions.

For Each rngCell In NetworkPatchRange
    If InStr(rngCell.Value, "-") > 0 Then
    rngCell.Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    rngCell.Offset(0, -1).Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    rngCell.Offset(0, -2).Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    End If
Next
Community
  • 1
  • 1
StrictlyBananas
  • 95
  • 8

1 Answers1

2

Avoid using Select in Excel VBA macros.

For Each rngCell In NetworkPatchRange
    If cbool(InStr(1, rngCell.Value, chr(45))) Then
        rngCell.offset(0, -2).resize(1, 3).interior.Pattern = xlNone
    End If
Next

The recorded macro code for removing a highlight performs more actions than is actually necessary. Just set the .interior.Pattern to xlNone.

It may be prudent to remember that manually removing a cell's 'Fill' (aka highlight) does not apply to cells that have been highlighted with a conditional formatting rule.

  • So, let me break yours down to see if I understand it. cbool is a boolean command that returns true or false, in this instance we want it to return true. chr(45) is referring to the ASCII value 45 which means "-". However, I don't understand why InStr starts off with "1". Also, rngCell.offset(0, -2)resize(1, 3) means we're going to the furthest offset value and resizing our offset range to include 3 cells to the right? – StrictlyBananas Jun 22 '17 at 20:01
  • 1. Instr has an optional starting position as the first parameter. It defaults to 1 so some people do not put it in; I do put it in always. 2. Yes, resize moves from left to right so we have to start at the left-most cell (with offset) so *move two columns to the left and make it three columns wide*.. –  Jun 22 '17 at 20:39
  • Thank you a ton. I'm fairly new to VBA and have been loving learning it. – StrictlyBananas Jun 22 '17 at 20:54