Set cell color based on another cell color

[Jobb1Excel]

Hey, im looking for a way to set cell color based on another cell color. I got 2 sheets and i use cell references from one sheet to the other.

I want the cells in sheet1 to be the same color (filled) as the cell references in the other sheet.

my cell references is pointed at the A row. So i want my =A1 cell to automatically be orange just like A1 in sheet2 and A3 to be white (Just some examples)
my cell with =A14 in sheet1 should be yellow.

 

[etaf]

how are the cells coloured - if using conditional formatting - then you can use the same rule - if not VBA and i do not provide VBA solutions

 

[SanjayGMusafir]

I believe there can be two ways to it... Anything better I shall also learn in process...

1. If you are using conditional formatting on source sheet, use same to do in destination sheet
2. OR, You can use VBA to copy paste only formats from source to destination sheet - But that will not be automatic, you have to initiate it manually at least once a day or as per the need.
   

 

[bobsan42]

Below are two procedures. read the comments in the code.

Sub matchAllCellsColors(Optional ByVal targetSheetName As String)
    'Loops through all used cells in the sheet and passes them to matchCellColorByReference for formatting
    Dim targetSheet As Worksheet
    If targetSheetName = "" Then
        Set targetSheet = ActiveSheet
    Else
        Set targetSheet = ThisWorkbook.Worksheets(targetSheetName)
    End If
    Dim cc As Range
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    For Each cc In targetSheet.UsedRange.SpecialCells(xlCellTypeFormulas).Cells
        matchCellColorByReference cc
    Next cc
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
    Set targetSheet = Nothing
    Set cc = Nothing
End Sub

Sub matchCellColorByReference(ByRef rng As Range)
    'Formats a single cell based on the reference in the formula
    'rng is a single cell range that has to be formatted
    'exits if does not find a formula
    'assumptions: _
        1. formula is as simple as =sheet_name!cell_address _
        2. reference cell is on another sheet in the same workbook _
        3. Colors in the reference sheet are applied to the cells WITHOUT ConditionalFormatting
       
    Dim ff As String
    ff = rng.Formula
    If Left(ff, 1) = "=" Then
        ff = Replace(ff, "=", "")
    Else 'not a formula
        Exit Sub
    End If
    Dim cc As Range, x As Variant
    Dim shName As String, cAddress As String
    x = Split(ff, "!")
    shName = Replace(x(0), "'", "")
    cAddress = x(1)
    On Error Resume Next
    Set cc = ThisWorkbook.Worksheets(shName).Range(cAddress)
    If cc Is Nothing Then GoTo exitPoint
    On Error GoTo 0
    x = cc.Interior.Color
    rng.Interior.Color = x
   
exitPoint:
    On Error Resume Next
    Set cc = Nothing
    x = Null
End Sub

Cell Coloring does not trigger an exposed event to allow easier automation of the process.
I believe some time ago I saw someone's solution on the subject, but that's about all I remember.
So the procedure has to be started manually.