CODEWITHSUNDEEP
vbaexcelexcel-2016
user3115933
user3115933

Reputation: 4443

How do I modify this VBA code so that it runs on all worksheets in my workbook?

I am using Excel 2016 and I have the following VBA codes which remove the highlighting in all cells (based on the RGB numbers provided) of the active worksheet.:

Sub RemoveSpecificColorFill()
'PURPOSE: Remove a specific fill color from the spreadsheet
 Dim cell As Range

'Turn off ScreenUpdating (speeds up code)
Application.ScreenUpdating = False

'Loop through each cell in the ActiveSheet
 For Each cell In ActiveSheet.UsedRange

'Check for a specific fill color
  If cell.Interior.Color = RGB(255, 255, 0) Then

    'Remove Fill Color
      cell.Interior.Color = xlNone

  End If

Next cell

End Sub

I want to update the codes so that the VBA codes run on all the worksheets in that workbook instead of the active one.

Upvotes: 0

Views: 80

Answers (4)

Barry
Barry

Reputation: 53

You should also turn screen updating back on after its finished i.e.

Sub RemoveSpecificColorFill()
'PURPOSE: Remove a specific fill color from the spreadsheet
 Dim cell As Range, wks As Worksheet

 'Turn off ScreenUpdating (speeds up code)
  Application.ScreenUpdating = False
  For Each wks In ThisWorkbook.Worksheets
  'Loop through each cell in the ActiveSheet
  For Each cell In wks.UsedRange

  'Check for a specific fill color
   If cell.Interior.Color = RGB(255, 255, 0) Then

   'Remove Fill Color
     cell.Interior.Color = xlNone

       End If

   Next cell
   Next wks
   Application.ScreenUpdating = True
   End Sub

Upvotes: 1

Olly
Olly

Reputation: 7891

This should be a bit quicker than looping through all cells:

Sub RemoveSpecificColorFill()
    Dim ws As Worksheet
    With Application
        .FindFormat.Clear
        .ReplaceFormat.Clear
        .FindFormat.Interior.Color = RGB(255, 255, 0)
        .ReplaceFormat.Interior.Color = xlNone
        For Each ws In ThisWorkbook.Worksheets
            ws.Cells.Replace What:="", Replacement:="", SearchFormat:=True, ReplaceFormat:=True
        Next ws
        .FindFormat.Clear
        .ReplaceFormat.Clear
    End With
End Sub

Upvotes: 4

Kippa2005
Kippa2005

Reputation: 41

This should work for you. It is just a sub that goes through each sheet and then runs your code

Sub forEachWs()
    application.screenupdating = false
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        Call RemoveSpecificColorFill(ws)
    Next
    application.screenupdating = true
End Sub

Sub RemoveSpecificColorFill(ws As Worksheet)
'PURPOSE: Remove a specific fill color from the spreadsheet
Dim cell As Range


'Loop through each cell in the ActiveSheet
 For Each cell In ws.UsedRange

'Check for a specific fill color
  If cell.Interior.Color = RGB(255, 255, 0) Then

    'Remove Fill Color
      cell.Interior.Color = xlNone

  End If

Next cell

End Sub

Upvotes: 1

IQV
IQV

Reputation: 500

You can add a second loop around your code and iterate through all worksheets of your workbook. Something like this

For Each ws In Worksheets

    For Each cell In ws.UsedRange

Upvotes: 1

Related Questions