VBA Modify Range when value is observed

Question

I'm trying to get a sub to work that will color fields based on when the values "TRUE" or "FALSE" appears. I've already asked the below question, and have arrived at the code, also below.

VBA Excel Format Range when value is found

Option Explicit

Public Sub MarkCellsAbove()
Dim ws   As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet2")
Dim v    As Variant
Dim i    As Long, j As Long, n As Long, m As Long, r As Long, y As Long
Dim rng  As Range
Dim rCell As Range
Dim DynamicArea As Range
Dim t    As Double


' get last row in column C
  n = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
' get last column from A
  y = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
' set dynamic area to above values
Set DynamicArea = ws.Range(Cells(1, 1), Cells(n, y))

' clear existing colors over the WHOLE column to minimize file size
  DynamicArea.Interior.ColorIndex = xlColorIndexNone

  For Each rCell In DynamicArea
  Select Case rCell.Text
          Case "TRUE"
            Set rng = rCell.Offset(-2, 0)
            rng.Interior.ColorIndex = 4

        Case "FALSE"
            Set rng = rCell.Offset(-2, 0)
            rng.Interior.ColorIndex = 5

        End Select
    Next


End Sub

This works well - I am able to color the cell 2 rows above where FALSE or TRUE is found. However - I would like to color not just this cell, but all cells in the range specified by Offset. So, if I specify 8 cells above, I would like to color 8 cells.

I hope someone can help - I'm so close to finishing this!

How do you want the number of cells specified - an input by the user or just added to the code? — SJR

FunThomas FunThomas · Accepted Answer · 2018-01-11T12:36:58

Try

Set rng = Range(rCell.Offset(-8, 0), rCell.Offset(-1, 0))

Note that you will get a runtime error if rCell is not at least in row 9

VBA Modify Range when value is observed

1 Answers