Conditional Formatting cell in Multiple Sheets

There are multiple files as sample file attached in a given folder.

In the following attached file ,Column F, following Conditional formatting required;

• Green Color filled to be removed,
• Cell with value BUY to be filled in Green and font color Red
• Cell with value SELL to be filled in Red and font color White
• The blank cell should have no color

Please

Quote from Mumps

Manually remove the green colour in column F if it exists. Then select all the cells with data in column F and create two conditional formatting rules using the following two formulae:

=F2="BUY" ---- select Green for fill format and Red in font colour format

=F2="SELL" ---- select Red for fill format and White in font colour format

Thanks Mumps for your advise, but as I mentioned there are multiple files in the given folder and all the files require the same formatting.

I need a VBA code to format all the files at one go.

Quote from Mumps

Place this macro in a separate workbook. The macro assumes that the sheet to be formatted will always be the first sheet in each workbook. Change the folder path (in red) to suit your needs.

Code

Sub FormatCells()
    Application.ScreenUpdating = False
    Dim wkbSource As Workbook, LastRow As Long
    Const strPath As String = "C:\Test\"
    ChDir strPath
    strExtension = Dir(strPath & "*.xlsx")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With Sheets(1)
            LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
            .Range("F2:F" & LastRow).Select
            Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=F2=""SELL"""
            Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
            With Selection.FormatConditions(1).Font
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = 0
            End With
            With Selection.FormatConditions(1).Interior
                .PatternColorIndex = xlAutomatic
                .Color = 255
                .TintAndShade = 0
            End With
            Selection.FormatConditions(1).StopIfTrue = False
            Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=F2=""BUY"""
            Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
            With Selection.FormatConditions(1).Font
                .Color = -16776961
                .TintAndShade = 0
            End With
            With Selection.FormatConditions(1).Interior
                .PatternColorIndex = xlAutomatic
                .Color = 5287936
                .TintAndShade = 0
            End With
            Selection.FormatConditions(1).StopIfTrue = False
            wkbSource.Close True
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

Display More

Thanks Mumps,

Its working perfectly.

But only one thing. It is not able to remove previous formatting of cells filled in Green color.

Quote from Mumps

Try:

Code

Sub FormatCells()
    Application.ScreenUpdating = False
    Dim wkbSource As Workbook, LastRow As Long
    Const strPath As String = "C:\Test\" 'change folder path to suit your needs
    ChDir strPath
    strExtension = Dir(strPath & "*.xlsx")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With Sheets(1)
            LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
            .Range("F2:F" & LastRow).Select
            With Selection
                .Interior.ColorIndex = xlNone
                .FormatConditions.Add Type:=xlExpression, Formula1:="=F2=""SELL"""
                .FormatConditions(.FormatConditions.Count).SetFirstPriority
            End With
            With Selection.FormatConditions(1).Font
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = 0
            End With
            With Selection.FormatConditions(1).Interior
                .PatternColorIndex = xlAutomatic
                .Color = 255
                .TintAndShade = 0
            End With
            With Selection
                .FormatConditions(1).StopIfTrue = False
                .FormatConditions.Add Type:=xlExpression, Formula1:="=F2=""BUY"""
                .FormatConditions(.FormatConditions.Count).SetFirstPriority
            End With
            With Selection.FormatConditions(1).Font
                .Color = -16776961
                .TintAndShade = 0
            End With
            With Selection.FormatConditions(1).Interior
                .PatternColorIndex = xlAutomatic
                .Color = 5287936
                .TintAndShade = 0
            End With
            Selection.FormatConditions(1).StopIfTrue = False
            wkbSource.Close True
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

Display More

Perfect.

Job Done.

You are Great Mumps