VBA Colour

[adurham22926192]

Hi all,

i have a selected range and i need a button linked to a macro to change the cell and font colour of lets say A10:I10 but the next time i click the button i need it to change the cell and font colour of A9:I9 and so on until it gets to the top.

Pls helpppp

 

[Scott Huish]

Perhaps something like this:

Sub ColorRows()
Dim x As Long
For x = 10 To 1 Step -1
    If Cells(x, 1).Interior.Color <> vbYellow Then
        Cells(x, 1).Resize(, 9).Interior.Color = vbYellow
        Exit For
    End If
Next
End Sub

 

[Peter_SSs]

Another possible interpretation (also only dealing with cell color here)

Sub Color_Rows()
  Dim oldrow As Long, newrow As Long
  
  Application.FindFormat.Clear
  Application.FindFormat.Interior.Color = vbYellow
  On Error Resume Next
  oldrow = Range("A1:A10").Find(What:="", SearchFormat:=True).Row
  On Error GoTo 0
  If oldrow < 2 Then
    oldrow = 1
    newrow = 10
  Else
    newrow = oldrow - 1
  End If
  Cells(oldrow, 1).Resize(, 9).Interior.Color = xlNone
  Cells(newrow, 1).Resize(, 9).Interior.Color = vbYellow
  Application.FindFormat.Clear
End Sub