Can I format a range of cells based upon another cell value

[dab1477]

Thanks in advance for the assist to a VBA newbie. I want to perform the following:

I want to color format cells A1:k:1 based upon the value entered into K1. I want to do this for each of 65 successive rows.
I can do this via conditional formatting for each series of cells, but I want to do this in one fell swoop using code.
My current conditional formatting statement looks like this for cell K1: $A$1="kaizen" then cells A1:k1 will format as fill in red with black font. I would like this to occur via VBA code. I don't know how to do this.

 

[SkipVought]

Hi,

You will not need VBA. Use Excel Conditional Formatting Wizard:

1) select ALL cells you want to format conditionally in ALL rows

2) open the CF wizard and use enter a formula option or something similar, don't have my Excel for reference.

3) enter this formula: note that ANY CF formula is entered with respect to the TOP ROW of you selection.
[tt]
=$K1="Kaizen"
[/tt]
4) select the FORMAT you want


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dab1477]

Thanks. I was able to do that with CF. I was attempting to learn if VBA could do this. You did help me with the cell ref issue. Thanks.

 

[PHV]

I was attempting to learn if VBA could do this
The macro recorder is your friend.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Dim rng as range, r as range

Set rng = ActiveSheet.usedrange

For each r in range(cells(1,1), cells(1,1).end(xldown))
   With intersect(r.entirerow, rng)
      If cells(r.row, "k").value = "Kaizen" then
          .interior.color = vbred
          .font.color = vbwhite
      Else
          .interior.color = vbwhite
          .font.color = vbblack
      End if
   End with
Next

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[combo]

You can use events to trace user actions. For changes in cell contents (i.e. formulas in cells, not their calculation results), add the code in worksheet's module:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
With Me
    If Not Intersect(Target, .Range("K1:K65")) Is Nothing Then
        r = Target.Row
        If .Range("A" & r).Value = "kaizen" Then
            .Range("A" & r & ":K" & r).Font.Color = vbRed
        Else
            .Range("A" & r & ":K" & r).Font.Color = vbBlack
        End If
    End If
End With
End Sub

Note that the code works for single cell changed. For values pasted in multiple cells the above code will pick the first row of changed range.

combo