none
VBA Beginner: Setting multiple non-contiguous cells as range RRS feed

  • Question

  • Self-taught VBA beginner, so please bare with me. I have a snippet of code to set conditional formatting to a range based upon value in the cell. As it's written, it applies this formatting to pretty much all of column K. While that experiment has been a success, I now only want to apply this conditional formatting to cells K7, K13, K19, K37, K43, K49. I am not sure how to set this, though. The Cells operator only seems to set one cell as active and the Range operator only seems to work with contiguous cells. And my google searching has turned up voluminous examples of ranges, but as yet I have not found a guide on how to set specific cells. Perhaps I searching the wrong term? 

    How should my coding look with the conditional formatting on just those specific cells?

    Sub ErrorCheck()
    Dim rg As Range
    Dim cond1 As FormatCondition, cond2 As FormatCondition
    Set rg = Range("K7", Range("K7").End(xlDown))
     
    'clear any existing conditional formatting
    rg.FormatConditions.Delete
    
    'define the rule for each conditional format
    Set cond1 = rg.FormatConditions.Add(xlCellValue, xlBetween, "=$i$7 + 2", "=$i$7 - 2")
    Set cond2 = rg.FormatConditions.Add(xlCellValue, xlNotBetween, "=$i$7 + 2", "=$i$7 - 2")
     
    'define the format applied for each conditional format
    With cond1
    .Interior.Color = vbGreen
    .Font.Color = vbBlack
    End With
    
    With cond2
    .Interior.Color = vbRed
    .Font.Color = vbWhite
    End With
     
    End Sub

    Friday, January 26, 2018 4:34 PM

Answers

  • Solved it. I was just using bad syntax. 

    Set rg = Range("K7, K13, K19, K37, K43, K49")

    • Marked as answer by DexterRat Friday, January 26, 2018 5:06 PM
    Friday, January 26, 2018 5:05 PM