CODEWITHSUNDEEP
vbaexcel
PeterS
PeterS

Reputation: 724

How to Fill Color in A cell in Workbook_BeforeSave

I currently have this code below:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

ThisWorkbook.Sheets("Sheet1").Cells(1, 1).Select

For i = 2 To 10
    If ThisWorkbook.Sheets("Sheet1").Cells(i, 1).Value = "YES" Then
        For j = 2 To 5
            If ThisWorkbook.Sheets("Sheet1").Cells(i, j).Value = "" Then
                **<insert format here>**
            End If
        Next j
    End If


    If ThisWorkbook.Sheets("Sheet1").Cells(i, 1).Value = "NO" Then
        For j = 2 To 5
            If ThisWorkbook.Sheets("Sheet1").Cells(i, j).Value = "" Then
                **<insert format here>**
            End If
        Next j
    End If
Next i

End Sub

Now. I tried to add the code below inside the If statement but didn't worked

ThisWorkbook.Sheets("Sheet1").Cells(i, j).Interior.ColorIndex = 3

How can we add fill color to the cells without value?

Upvotes: 1

Views: 106

Answers (2)

Vityata
Vityata

Reputation: 43585

Try with this one (before save):

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ThisWorkbook.Sheets("Tabelle1").Cells(1, 1).Select

    For i = 2 To 10
        If ThisWorkbook.Sheets("Tabelle1").Cells(i, 1).Value = "YES" Then
            For j = 2 To 5
                If ThisWorkbook.Sheets("Tabelle1").Cells(i, j).Value = "" Then
                    ThisWorkbook.Sheets("Tabelle1").Cells(i, j).Interior.ColorIndex = 3
                    '**<insert format here>**
                End If
            Next j
        End If


        If ThisWorkbook.Sheets("Tabelle1").Cells(i, 1).Value = "" Then
            For j = 2 To 5
                If ThisWorkbook.Sheets("Tabelle1").Cells(i, j).Value = "" Then
                    ThisWorkbook.Sheets("Tabelle1").Cells(i, j).Interior.ColorIndex = 3
                    '**<insert format here>**
                End If
            Next j
        End If
    Next i

End Sub

Upvotes: 1

user3598756
user3598756

Reputation: 29421

Interior.ColorIndex must work on range

maybe is the logic that doesn't work as expected

try this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim i As Long

    With ThisWorkbook.Sheets("Sheet1")
        For i = 2 To 10
            Select Case .Cells(i, 1).value
                Case "YES"
                    If WorksheetFunction.CountA(.Cells(i, 2).Resize(, 4)) < 4 Then .Cells(i, 2).Resize(, 4).SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 3
                Case "NO"
                    If WorksheetFunction.CountA(.Cells(i, 2).Resize(, 4)) < 4 Then .Cells(i, 2).Resize(, 4).SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 2
            End Select
        Next i
    End With
End Sub

Upvotes: 2

Related Questions