Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Aug 2003
    Green Bay, Wisconsin, USA
    Thanked 0 Times in 0 Posts

    Excel 2010 formula to shade cell to match another cell

    We have one worksheet where some cells have been manually colored (fill). On another worksheet we want to color cells based on the color of cells on the first worksheet, not depending on the value of either cell.

    I couldn't find any functions that will determine the color or a way in Conditional Formatting to color to match another color.

    Does anyone have any knowledge of how this could be done?

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Thanked 105 Times in 90 Posts
    Without using VBA you can only do what you want manually by using the Format Painter or the Paste Special . . . Formats dialog.

    If you are comfortable using VBA then it is fairly simple to read the Colour Index property of the original cell and copy that property to the target cell. I say "fairly" because although there are millions of possible colours, each workbook is limited to 56 actual colours which are particular to that workbook depending on what manual colouring has been done. That is to say that this method will work, as you want it to, within a workbook, but can have surprising results when transferring a colour index from one workbook to another.

    In VBA the property you need to transfer from one cell to another is Range("cell reference").Interior.colorindex. There is an example here:
    Last edited by MartinM; 2012-09-20 at 04:59.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2003
    Green Bay, Wisconsin, USA
    Thanked 0 Times in 0 Posts
    Thanks, Martin.

    I am not up to speed using VBA. I see from the code that Interior.colorindex will set the color, but there could be different colors on the worksheet. When we enter a formula that refers to a cell on a different worksheet, we want the color of the cell containing the formula to be the same color as the cell the formula refers to. Any ideas?

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Manning, South Carolina
    Thanked 1,454 Times in 1,323 Posts

    Here's some code that will grab the color number vs the colorindex and should always work {fingers crossed}.
    Option Explicit
    Sub GrabColors()
       Dim wkbSource As Workbook
       Dim wkbDest   As Workbook
       Dim lColor    As Long
       Dim zPath     As String
       Dim zFN       As String
       '**** Setup ****
       zPath = "G:\BEKDocs\Excel\Test\"   '*** Your Drive Path Here   ***
       zFN = "ColorBook1.xlsx"            '*** Your FileName.ext Here ***
       Set wkbDest = ActiveWorkbook
       Set wkbSource = Workbooks.Open(Filename:=zPath & zFN, ReadOnly:=True)
      '*** End Setup ***
       lColor = Range("E1").Interior.Color
       Range("E1").Interior.Color = lColor
      '*** Cleanup ***
       Set wkbSource = Nothing
       Set wkbDest = Nothing
    End Sub
    • The code must resided in the file you are transferring the colors to and that file must be of type .xlsm {macro enabled}.
    • You need to change the source file drive:\path\filename.ext to use your file.
    • You can change the code between the '*** End Setup *** and '*** Cleanup *** comments to copy as many colors as you want between the two workbooks.

    Last edited by RetiredGeek; 2012-09-20 at 13:46.
    May the Forces of good computing be with you!


    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts