Simple Loop for Formatting Headers

[SteinDA]

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!

 

[mohadin]

Hi,
Try

[COLOR=#333333]Range("A1:D1", Range("C1").End(xlToRight)).Interior.Color =[/COLOR][FONT=Arial]RGB(139,0,139)[/FONT]

 

[Rick Rothstein]

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.

 

[SteinDA]

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?

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!

 

[Fluff]

You need to qualify the ranges like

Ws.Range("A1:D1", Ws.Range("C1").End(xlToRight)).Interior.Color = RGB(139, 0, 139)

otherwise it will only format the active sheet.

 

[SteinDA]

You need to qualify the ranges like

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?

If WS.Name <> "Sheet1" Then

Thanks again!

 

[Fluff]

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.

 

[SteinDA]

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!

 

[Fluff]

Glad it's sorted & thanks for the feedback