CODEWITHSUNDEEP
excelvba
sturdy267
sturdy267

Reputation: 79

Macro to highlight rows of data does not run in Excel workbook

I just received an answer to my question re: code to highlight rows of data in a recent post. Here is the link to the post: Shading Row Post

The code that I am using is from that post and it works in my test wkbk in Excel 2013 that has one wksht and is saved on my desktop.

The wkbk I need to paste the code into has many wkshts and is normally a macro enabled shared workbook on a network drive, but I have taken it off of Share to add this code. (I have other code in a different module in this wkbk that resizes comments and it works.) I need this highlighting code mainly for wksht 3 named Schedule, but if it can be applied to other wkshts, that would be ideal.

What I've tried:

  1. While in VBA, I've inserted a 2nd module (Module 2) under my wkbk. I've compiled it, saved it, and ran it under Developer tab>Macro and nothing happened.
  2. While in VBA, I've right clicked This Worksheet>View Code and pasted the code there, saved it, and ran it same as above and nothing happened.
  3. I've also right clicked the Schedule wksht and pasted the code there, saved it, etc., same thing--nothing happened.
  4. Also, I've used all drop downs in the Macros In: part of the Macros dialog box: This Workbooks, All Open Workbooks, and another one that is the name of my workbook.
  5. I've not changed my macro settings--they have always been on Disable All Macros with Notification.

It's not doing anything to the other wkshts by mistake either. I've checked.

I would appreciate help. Here is my project window:

VBA Project window

Sub colorNew()
Dim r As Long
Dim colourIt As Boolean
Dim colour As XlColorIndex

colourIt = False

With ActiveSheet
    r = 2  ' First row of data

    Do While .Cells(r, "B").Value <> ""
        'See if value has changed
        If .Cells(r, "B").Value <> .Cells(r - 1, "B").Value Or _
           .Cells(r, "C").Value <> .Cells(r - 1, "C").Value Then
            colourIt = Not colourIt
        End If

        'Determine which colour to use on this row
        If colourIt Then
            colour = RGB(252, 228, 214)
        Else
            colour = xlColorIndexNone
        End If
        'Apply the colouring
        .Range(.Cells(r, "A"), .Cells(r, .Cells(r, .Columns.Count).End(xlToLeft).Column)).Interior.Color = colour

        'Point to the next row of data
        r = r + 1
    Loop
End With
End Sub

Upvotes: 1

Views: 327

Answers (3)

YowE3K
YowE3K

Reputation: 23974

Is anything in cell B2 of the worksheet that was active when you ran the code?

The macro starts on row 2, and stops as soon as a row has nothing in column B, so if B2 is empty it will stop immediately.

Upvotes: 1

Animesh
Animesh

Reputation: 228

Sub colorNew()
Dim r As Long
Dim colourIt As Boolean
Dim colour As XlColorIndex

colourIt = False

With ThisWorkbook.Sheets("Schedule")

    r = 2  ' First row of data

    Do While .Cells(r, "B").Value <> ""
        'See if value has changed
        If .Cells(r, "B").Value <> .Cells(r - 1, "B").Value Or _
           .Cells(r, "C").Value <> .Cells(r - 1, "C").Value Then
            colourIt = Not colourIt
        End If

        'Determine which colour to use on this row
        If colourIt Then
            colour = RGB(252, 228, 214)
        Else
            colour = xlColorIndexNone
        End If
        'Apply the colouring
        .Range(.Cells(r, "A"), .Cells(r, .Cells(r, .Columns.Count).End(xlToLeft).Column)).Interior.Color = colour
        'Point to the next row of data
        r = r + 1
    Loop
End With
End Sub

Upvotes: 1

Werrf
Werrf

Reputation: 1148

You're right about putting the code into a module. Make sure the code ONLY appears in the module, not anywhere else, then try this.

Go to the first executable line colourIt = False, and hit F9. This will insert a break point into the window (a red dot on the left and a red highlight across the line). Once the break point is there, try running the code.

If the macro is running, it will stop at this line and highlight it in yellow (just as if there was an error there). If no yellow highlight appears, the macro isn't running. This information will help you narrow down what the problem is - is it not running, or is it running but not doing anything? When you're done, you can hit F5 to continue running, or you can use the Stop button in the toolbar to stop the macro.

If it's running but not doing anything, try changing your Apply line to a With block and add an additional line:

With .Range(.cells(r,"A") ,... etc).interior
    .color = colour
    .pattern = xlSolid
End With

Once you're done, go back to colourIt = False and hit F9 again to clear the break point.

Good luck!

Upvotes: 1

Related Questions