Set cell color based on another cell color

Jobb1Excel

New Member
Joined
Oct 17, 2023
Messages
22
Office Version
  1. 365
Platform
  1. Windows
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.

1702296800762.png


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.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
 
Upvote 0
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.
 
Upvote 0
Below are two procedures. read the comments in the code.
VBA 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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,260
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top