How to use a cell range to color another one?

Question

I have used conditional formatting to color cells with numbers (column A):

Now i want to reproduce this formatting on a text cell range (column B).

I have tried the vba following code but it does now work:

Worksheets("Sheet1").Range("B1:B4").Interior.Color = 
Worksheets("Sheet1").Range("A1:A4").Interior.Color

Answer 1

Interior defines the formatting of a cell. However, this can be overruled by conditional formatting. The actual color (and also other formatting) of a cell can be read using the property DisplayFormat.

You will need to copy the format cell by cell, you can't copy it in one go:

With ThisWorkbook.Sheets("Sheet1")
    Dim sourceRange As Range
    Set sourceRange = Intersect(.Range("A1").CurrentRegion, .Columns("A:A"))
    Dim cell As Range
    For Each cell In sourceRange
        cell.Offset(0, 1).Interior.Color = cell.DisplayFormat.Interior.Color
    Next cell
End With

Now there's only one thing to solve: When do you call this routine. As you probably want that column B is updated automatically, you could for example put the code into the Change-Event of the sheet. As you have to react only to a change in column A, you don't need to reformat all cells in B but only the cells in B where a value in A was changed.

Note that the code must be put into the Worksheet module of Sheet1, else it will not be triggered:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    For Each cell In Target
        If Not Intersect(cell, Columns("A:A")) Is Nothing Then
            cell.Offset(0, 1).Interior.Color = cell.DisplayFormat.Interior.Color
        End If
    Next cell
End Sub