Microsoft Excel - Cell >0 make row yellow Loop

Asked By Dan on 22-Dec-11 12:23 PM
I am trying to create a macro instead of using conditional formatting.  Looking at column B:B, if any cell is greater than 0, then make the entire row yellow and do until empty row is found.

Dan
John D replied to Dan on 22-Dec-11 01:03 PM
Hi Dan
Try this , it's simple and will color the entire row yellow if >0 and runs quicker then looping.
Sub colorit()
For Each cell In Range("B:B")
    If cell.Value > 0 Then
      cell.EntireRow.Interior.ColorIndex = 6
    End If
Next
End Sub
John
Dan replied to John D on 22-Dec-11 01:16 PM
How can I make it stop when it finds the first empty cell in B:B?  Nevermind it stopped.  The only problem is highlighting the row one which has text in it.  I dont want the first row to be highlighted.

LOL, I figured it out!  Thank you very much for your help, I greatly appreciate it.
John D replied to Dan on 22-Dec-11 01:20 PM
You're welcome
Happy holidays
John
Devil Scorpio replied to Dan on 22-Dec-11 01:26 PM
HI,

You can stop the loop by using Exit Sub when cell is Empty

Sub colorit()
For Each cell In Range("B:B")
    If cell.Value > 0 Then
    cell.EntireRow.Interior.ColorIndex = 6
   Else If IsEmpty(cell.Value) Then
      Exit Sub 

    End If
Next
End Sub
John D replied to Devil Scorpio on 22-Dec-11 02:01 PM
Hi
Your Elseif should be one word in this case otherwise you'll get an error, and it will stop at the first empty row.
Regards
John
John D replied to John D on 22-Dec-11 02:09 PM
Typo...
Ignore the first message.
Your Elseif should be one word in this case otherwise you'll get an error,

Regards
John
Jitendra Faye replied to Dan on 22-Dec-11 10:52 PM
Follow this example-





Macro Code:

The macro code looks like this:

Sub Update_Row_Colors()

    Dim LRow As Integer
    Dim LCell As String
    Dim LColorCells As String
    'Start at row 7
    LRow = 7

    'Update row colors for the first 2000 rows
    While LRow < 2000
      LCell = "C" & LRow
      'Color will changed in columns A to K
      LColorCells = "A" & LRow & ":" & "K" & LRow

      Select Case Left(Range(LCell).Value, 6)

        'Set row color to light blue
        Case "007007"
          Range(LColorCells).Interior.ColorIndex = 34
          Range(LColorCells).Interior.Pattern = xlSolid

        'Set row color to light green
        Case "030087"
          Rows(LRow & ":" & LRow).Select
          Range(LColorCells).Interior.ColorIndex = 35
          Range(LColorCells).Interior.Pattern = xlSolid

        'Set row color to light yellow
        Case "063599"
          Rows(LRow & ":" & LRow).Select
          Range(LColorCells).Interior.ColorIndex = 19
          Range(LColorCells).Interior.Pattern = xlSolid

        'Default all other rows to no color
        Case Else
          Rows(LRow & ":" & LRow).Select
          Range(LColorCells).Interior.ColorIndex = xlNone

      End Select

      LRow = LRow + 1
    Wend

    Range("A1").Select

End Sub




Hope this will help you.