Simple Loop for Formatting Headers

SteinDA

New Member
Joined
Sep 23, 2019
Messages
6
Hi,

Beyond new to VBA and macros. I'm trying to put together some simple code to loop through all sheets, excluding sheet 1, to format a varied range of cells in each sheet (if the cell has text, format it).

I've been reading through all the forums and it seems like it should be simple. But clearly not as simple as it seems....at least for someone like me.

code

Sub FormatSheets()




Dim WS As Worksheet


For Each WS In ThisWorkbook.Worksheets
If WS <> "Sheet1" Then
Range("A1:D1", Range("C1").End(xlToRight)).Interior.Color = rgbDarkMagenta


End If
Next


End Sub

/code

The best I can get is sheet 1, which I'm trying to exclude have its header formatted. The code loops through the other cells, but does not actually format them.

Any help would be greatly appreciated.

Thank you!
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Hi,
Try

Code:
[COLOR=#333333]Range("A1:D1", Range("C1").End(xlToRight)).Interior.Color =[/COLOR][FONT=Arial]RGB(139,0,139)[/FONT]
 
Last edited:
Upvote 0
Rich (BB code):
Sub FormatSheets()
  Dim WS As Worksheet
    For Each WS In ThisWorkbook.Worksheets
      If WS.Name <> "Sheet1" Then
    .....
    .....
In addition to the change mohadin suggested, make the change I show in red above as well.
 
Last edited:
Upvote 0
Thank you both for your help.

I realized I pasted the module I was playing around in to try and get it to work rather than what I had set and functioning (which looks more like what you shared with me).

However, with the below code, it still formats the first row of Sheet 1, which is supposed to be ignored, and then loops through the other sheets, but doesn't actually format them.

My goal is to ignore sheet 1, and then format the remaining sheets. Do I need to activate or select the remaining sheets (an unknown count) in any other way?

Code:
Sub FormatSheets()




    Dim WS As Worksheet
    Dim Header As Range
                 
       For Each WS In ThisWorkbook.Worksheets
    
            If WS.Name <> "Sheet1" Then
        
                 Range("A1:D1", Range("C1").End(xlToRight)).Interior.Color = RGB(139, 0, 139)


                                                                
                                                                
          End If
       Next

End Sub

Thanks again!
 
Upvote 0
You need to qualify the ranges like
Code:
Ws.Range("A1:D1", Ws.Range("C1").End(xlToRight)).Interior.Color = RGB(139, 0, 139)
otherwise it will only format the active sheet.
 
Upvote 0
You need to qualify the ranges like
Code:
Ws.Range("A1:D1", Ws.Range("C1").End(xlToRight)).Interior.Color = RGB(139, 0, 139)

Yes ! Thank you so much! That's what I've been missing. Going through what felts like hundreds of pages of searches did not find me that answer.

I'm still having the issues with excluding sheet 1. Do you see an issue with the code below that might prevent it working? Or could something elsewhere in the code prevent it?

Code:
If WS.Name <> "Sheet1" Then

Thanks again!
 
Upvote 0
As long as the name on the sheet tab says Sheet1 it should be ignored.
If that is the name then check for any leading/trailing spaces.
 
Upvote 0
They were both definitely "Sheet1", but there must've been some issue with the characters. When I copied/pasted from (Name) into the macro, it finally worked.

Thank you so much!
 
Upvote 0
Glad it's sorted & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top