Fill background colour of only cells which contain data using VBA?

Question

I have a worksheet that I use VBA to fill with data by opening a delimited txt file and importing the data while applying various formulas.

The columns that contain data always remain static: Columns A:H

This works perfectly.

However, I have been struggling to workout a way of applying a specific background colour to only cells that contain data.

The number of cells that can have data changes with each txt file import so I need VBA to check each cell for data and than only apply the background colour to those cells.

I know how to apply a background to a specific range but its the "ever changing" dynamic part I am unable to work out?

Is your issue [how to find the last cell](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba)? — BigBen, Apr 08 '21 at 19:12

score -1 · Answer 1 · edited Apr 11 '21 at 09:14

Personally I would use conditional formatting, but as you have requested help with VBA I have this code which should do as you request.

This code will find the range for columns A to H and the last used row, then check each cell in the range to see if it has a value, if so add a background colour to that cell.

Sub ColourCell()

' find the last used row in the range from A to H

Dim Col As Integer: Col = 1 ' Starting Column
Dim LRow As Long
Dim LRowRef As Long
  
For i = 1 To 8  ' For each column A to H

    LRow = Cells(Rows.Count, Col).End(xlUp).Row ' find last cell used in column

    If LRow > LRowRef Then ' Check to see if the LRow is greater than previous rows
        LRowRef = LRow ' if it is larger than previous then store this value
    End If

    Col = Col + 1

Next i

' Change the cell background colours that contain values

Dim Rng As Range: Set Rng = Range("A1:H" & LRowRef) ' Define the range
Dim CellCheck As Range

For Each CellCheck In Rng.Cells ' Run through each cell in range

    If CellCheck.Value <> "" Then ' Check to see if the cell contains a value
        CellCheck.Interior.Color = RGB(255, 255, 0) ' If it does, set the background Colour
    End If
    
Next CellCheck

End Sub

this code does not reference the workbook or sheet, so it will only run on the selected sheet, you will need to amend the code to reference the sheet you would like it to run on.

Fill background colour of only cells which contain data using VBA?

1 Answers1