CODEWITHSUNDEEP
vbaexcelconditional-formatting
Brandon Lebedev
Brandon Lebedev

Reputation: 2797

Conditional Formatting: Global Formatting Definitions?

Lets say I have Sheet1 where

    A    B
1   foo  foo
2   bar  bar

and ReferenceSheet where

    A
1   bar  <-- cell is formatted with red background
2   foo  <-- cell is formatted with blue background

and where, as in all other programming examples, "bar" and "foo" are arbitrary placeholder values. :)

I would like to make it so that for each cell (A1:D4) in Sheet1 that matches the value of a definition cell in ReferenceSheet (A1:A2), the cell will automatically copy the formatting of the definition cell.

So that, continuing with the above example, Sheet1's background colors will be formatted as

    A    B
1   bl   bl
2   red  red

But if I change the ReferenceSheet's formatting to 

    A
1   bar  <-- cell is formatted with pink background
2   foo  <-- cell is formatted with black background

Sheet1's background formatting will update to

    A    B
1   blk  blk
2   pk   pk

If possible, how does one go about implementing this?

======

*I would like to define my formatting in a sheet (rather than in Visual Basic code) in order to create more user-accessible formatting definitions. If that's not an option, Visual Basic (and therefore global) definitions would be my next choice. Mass-copied conditional formatting applied to each cell would be my last choice, though finding a style-abstraction option similar to styles in Microsoft Word would make it a more paletable alternative.

Upvotes: 1

Views: 723

Answers (1)

Chrismas007
Chrismas007

Reputation: 6105

A Worksheet_Change Sub will do what you are looking for:

Private Sub Worksheet_Change(ByVal Target As Range)
    'Place Sub in only the Reference Worksheet

   'Do nothing if more than one cell is changed or content deleted
   If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

   With Sheets("Sheet1").Range("DefineRange").FormatConditions.Modify(Type:=xlCellValue, Operator:=xlEqual, Formula1:="=" & Target.Value)
     .Interior.Color = Target.Interior.Color
   End With

End Sub

Edit: I used .Modify because I assume you will already have a conditional format specified if a cell equals a certain value. If you want to add a new conditional formatting rule, use .Add instead.

Upvotes: 0

Related Questions