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!
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

mohadin

Active Member
Joined
Mar 22, 2015
Messages
361
Office Version
2013
Platform
Windows
Hi,
Try

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,031
Office Version
2010
Platform
Windows
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:

SteinDA

New Member
Joined
Sep 23, 2019
Messages
6
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!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,543
Office Version
365
Platform
Windows
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.
 

SteinDA

New Member
Joined
Sep 23, 2019
Messages
6
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!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,543
Office Version
365
Platform
Windows
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

New Member
Joined
Sep 23, 2019
Messages
6
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,543
Office Version
365
Platform
Windows
Glad it's sorted & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,102,152
Messages
5,485,058
Members
407,479
Latest member
jbone2020

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top