Invoke VBA to autofit active worksheet and update cell values of PASS with green color and FAIL as red color in "ColumnA"

Help Studio
, ,
1

I am using and running this below macro to update excel sheet to autofit columns and color for pass/fail in column A… its giving me typemismatch error when running process. Can you please help

Sub FormatStatusColumn()
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range

Set ws = ActiveSheet
Set rng = ws.Range("Status1:Status" & ws.Cells(ws.Rows.Count, "Status").End(xlUp).Row)

With rng
    ' Set font color and background color based on cell value
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=PASS"
    .FormatConditions(.FormatConditions.Count).Interior.Color = vbGreen
    .FormatConditions(.FormatConditions.Count).Font.Color = vbWhite
    
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=FAIL"
    .FormatConditions(.FormatConditions.Count).Interior.Color = vbRed
    .FormatConditions(.FormatConditions.Count).Font.Color = vbWhite
    
    ' Auto-fit column width based on cell contents
    .EntireColumn.AutoFit
End With

End Sub

1 Like
2

Hi @r0818 ,

Please share the excel sample you’re trying to this on.

3

Sampleogs_20230411_103311.xls (26.5 KB)

4

This does not show Pass/Fail in columnA?

image
image1296×282 10.9 KB

5

Column D will show success or error. trying to format entire sheet autofit and success cell as green color and ERROR as red color … can you please help…

6

Try this scrip:

Sub AutofitAndColorColumns()
    'Autofit all columns in the active sheet
    Cells.EntireColumn.AutoFit
    
    'Get the last row in the sheet
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "D").End(xlUp).Row
    
    'Loop through all the rows in column D and change the color accordingly
    For i = 1 To lastRow
        If Cells(i, "D").Value = "ERROR" Then
            'If the value in column D is "ERROR", set the color to red
            Cells(i, "D").Interior.Color = RGB(255, 0, 0)
        ElseIf Cells(i, "D").Value = "Success" Then
            'If the value in column D is "Success", set the color to green
            Cells(i, "D").Interior.Color = RGB(0, 255, 0)
        Else
            'If the value in column D is anything else, leave the column uncolored
            Cells(i, "D").Interior.ColorIndex = xlNone
        End If
    Next i
End Sub

The above VBA script does the following:

  1. autofit all columns
  2. Changes the color in column D
    a) if the value equals to ‘ERROR’ makes the color red
    b) Else if the value equals to ‘Success’ then makes the color green
    c) Else leaves the column uncolored.

image
image1612×267 12.6 KB

2 Likes
7

thank you for your response. its working

8

I am glad I could help, please do not forget to mark it as a solution.

1 Like