Find/replace limited to one column but many worksheets

Question

I'll start by saying the only VBA I know is trial and error from manipulating recorded macros. I am a CPA trying to learn VBA the hard way (and wishing I had gone to school for computer programming instead!).

I have large workbooks with multiple worksheets. Cells highlighted yellow in column G need to be formatted in a specific way in order for the file to import correctly to a web-based program. They need to remain highlighted yellow, be right/bottom aligned, and custom format of mm/dd/yyyy. I recorded a macro doing find/replace to try to replace all yellow highlighted cells within column G with highlighted yellow, bottom/right justified, custom format mm/dd/yyyy, but it's not limiting the replace to only column G. I also have no clue how to get the macro to loop through all the worksheets before finishing. Help?!

This is what I have from my basic macro recording:

Sub Macro2()
'
' Macro2 Macro
'

'
    Columns("G:G").Select
    Range("G:G").Activate
    With Application.FindFormat.Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Application.ReplaceFormat.Clear
    Application.ReplaceFormat.NumberFormat = "mm/dd/yyyy"
    With Application.ReplaceFormat
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
    End With
    With Application.ReplaceFormat.Font
        .Subscript = False
        .TintAndShade = 0
    End With
    With Application.ReplaceFormat.Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
End Sub

EDITED POST TO ADD: Please see screenshot of a typical sheet I am trying to reformat. Again, I only need to worry about changing the formatting on cells that are yellow highlighted, but I'm still having trouble limiting the find/replace to column G only... [1]: [https://i.sstatic.net/wRu30.jpg]

Answer 1

Here's some code that seems to do what you describe. I've placed a lot of .select statements in the code so that you can learn how it works by stepping through it, but you should remove all those once you understand. Also, I have some commented out code at the bottom which you can use to loop through multiple sheets. The animated gif shows the code running on an example I made up. Let me know if you have questions.

Sub reformat()
Dim sh As Worksheet, r As Range, cell As Range
  Set sh = ActiveSheet
  Set r = sh.Range("G1")
  r.Select
  If r.Offset(1, 0) <> "" Then Set r = sh.Range(r, r.End(xlDown))
  r.Select
  For Each cell In r
    With cell
      .Select
      If .Interior.Color = 65535 Then
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .NumberFormat = "mm/dd/yyyy"
      End If
    End With
  Next
For Each sh In ThisWorkbook.Worksheets
'place the above code in this loop if you want
'to apply the above to all worksheets in the workbook
'also remove the set sh=ActiveSheet line
Next sh
End Sub

Answer 2

Cycle through each worksheet in the active workbook then AutoFilter on the cell color then apply the changes to the visible cells..

sub yellowSpecial()
    dim w as long

    with activeworkbook
        for w=1 to .worksheets.count
            with worksheets(w)
                if .autofiltermode then .autofiltermode = false
                with .range(.cells(1, "G"), .cells(.rows.count, "G").end(xlup))
                    .autofilter field:=1, criteria1:=vbyellow, operator:=xlFilterCellColor
                    with .resize(.rows.count-1, .columns.count).offset(1,0)
                        if cbool(application.subtotal(103,.cells)) then
                            with .specialcells(xlcelltypevisible)
                                .HorizontalAlignment = xlRight
                                .VerticalAlignment = xlBottom
                                .numberformat = "mm/dd/yyyy"
                            end with
                        end if
                    end with
                end with
                if .autofiltermode then .autofiltermode = false
            end with
        next w
    end with
end sub

Answer 3

If you are trying to make sense of recorded code, the first thing to do is get rid of all the extraneous, verbose code that was added but doesn't do anything. Recorded code covers all aspects of an operation whether you require them or not.

This is a rewrite of your original using only what is required.

Sub yellowSpecialReplace()
    Dim w As Long

    Application.DisplayAlerts = False

    With Application.FindFormat
        .Clear
        .Interior.Color = 65535
    End With
    With Application.ReplaceFormat
        .Clear
        .NumberFormat = "mm/dd/yyyy"
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
    End With

    With ActiveWorkbook
        For w = 1 To .Worksheets.Count
            With Worksheets(w).Columns("G:G")
                .Cells.Replace What:=vbNullString, Replacement:=vbNullString, _
                               LookAt:=xlPart, SearchOrder:=xlByRows, _
                               SearchFormat:=True, ReplaceFormat:=True
            End With
        Next w
    End With

    Application.DisplayAlerts = True
End Sub