I've got a spreadsheet in which all of the data on the sheet is continuously updating based on data located elsewhere in the document.

What I need to do is establish a code with which I can dictate the formatting of various cells in a row (specific formatting for each column) based on the cell value of another cell within that row.

For example:

When the cell value of G10 = "totals"

I need cells A10, B10, & C10 to be bold, red and font size 20

...End Sub


Now this is just an example, but there are various conditions like this that I need to have apply to the sheet every time anything on the sheet changes.

I've come close with the following codes, but each is lacking something and I've failed to remedy the problems I list under each:

Code 1:


Private Sub Worksheet_Change(ByVal Target As Range)

Dim r As Range, c As Range

Set r = Worksheets("sheet 1").Range("C220:P2000")

For Each c In r
With c
If Not IsError(.Value) Then
Select Case .Value

Case "TOTALS - - >"
.Font.ColorIndex = 1
.Font.Bold = True
.Font.Size = 20

Case "SUBTOTALS - - >"
.Font.ColorIndex = 1
.Font.Bold = True
.Font.Size = 15

End Select
End If
End With
Next c

End Sub
NOTE: This works, but only for the cells "c" for which the value is either "totals - ->" or "subtotals - - >". I need to have othercell formatting change also, not just the cell in which the value is located.


CODE 2:


Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range

If Target.Column = 7 And Target.Row > 206 Then

Set rng = Range(Cells(Target.Row, 4), Cells(Target.Row, 6))
With rng
Select Case Target.Value

Case "totals - - >"
.Font.Size = 30
.Interior.ColorIndex = 8
.Font.Bold = True


End Select
Set rng = Nothing

End With
End If

End Sub
NOTE: ...Literally nothing happens. Now in this case it's only going to be the rows below 206 that the changes apply to (which explains the Target.Row > 206), but I've yet to make this even register a change. What I need is for the macro to run every time anything on the sheet changes, but I may be messing that up somewhere here. Not sure (noob).


If anyone can help me regain my sanity I would be forever in their debt. Though posting a code that works would solve my problem, any comments on why the above code is failing to satisfy my needs would be much appreciated too. Thank you so much in advance.