Results 1 to 6 of 6

Cell colour change to match that to its left

Threaded View

  1. #2
    Valued Forum Contributor
    Join Date
    03-16-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    371

    Re: Cell colour change to match that to its left

    There is no worksheet event that triggers on a format change (e.g. background fill). However, the code below achieves what you want, you just need to find a worksheet event that you are happy with.

    Option Explicit
    
    Sub t()
    
        Dim rColA As Range
        Dim rColB As Range
        Dim rColC As Range
        Dim rCell As Range
        Dim i     As Integer
        
        With ActiveSheet
            Set rColA = Intersect(Columns("A"), .UsedRange)
            Set rColB = rColA.Offset(0, 1)
            Set rColC = rColA.Offset(0, 2)
        End With
        
        For i = 1 To rColA.Rows.Count
            Set rCell = rColA.Cells(i, 1)
            If rCell.Interior.Pattern <> xlNone Then
                rColB.Cells(i, 1).Interior.Color = rCell.Interior.Color
                rColC.Cells(i, 1).Interior.Color = rCell.Interior.Color
            End If
            rColB.Cells(i, 1).Interior.Pattern = rCell.Interior.Pattern
            rColC.Cells(i, 1).Interior.Pattern = rCell.Interior.Pattern
        Next i
        
    End Sub
    Last edited by Stormin'; 08-18-2017 at 07:10 AM. Reason: Error in code
    Design everything to be as simple as possible, but no simpler.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Change background colour to yellow if cell doesn't match the previous cell
    By drzeto in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-10-2017, 05:33 PM
  2. How to change cell colour, if the colour is based on value from formula?
    By darah237 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-19-2016, 11:40 AM
  3. Replies: 6
    Last Post: 04-11-2016, 09:48 AM
  4. Change colour of cells based on another cell's colour (Not value)
    By LTrain89 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-25-2013, 08:44 PM
  5. Replies: 2
    Last Post: 06-07-2011, 07:00 AM
  6. Colour change column chart based on cell colour
    By Alice21 in forum Excel General
    Replies: 11
    Last Post: 04-05-2011, 10:10 AM
  7. change a cell background colour to my own RGB colour requirements
    By Stephen Doughty in forum Excel General
    Replies: 4
    Last Post: 06-16-2006, 08:15 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1