Conditional Formatting Using The Format Of Specific Cell

[Draperies]

I am using Excel 2007 and I have a series of rules for conditional formatting that work great. They are all under the "Use a formula to determine which cells to format", in which I can select the exact format of the way I want the cells that fit the rule to appear. However, is there anyway I can have the format from a specific cell located on the sheet applied to the cells instead of having to specifically set the format in the settings for each rule? As far as I can tell this doesn't seem possible but if anyone can confirm this or let me know a way in which it can be done it'd be greatly appreciated! Thanks!

 

[Jerry Sullivan]

I'm not aware of any way to do that by directly referencing the cell with the desired format.

You could use VBA to transfer specified formatting settings from your
example format cell (A1 in the Example below) to your FormatConditions
for a specified range and rule.

In this example, a rule is applied to the range named CFRange to format cells
that contain a 7. The formatting is being transfered from Cell A1.

<b>Transfer Formatting</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:78px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; color:#ff0000; font-style:italic; text-decoration:underline; text-align:right; ">123456.789</td><td > </td><td style="background-color:#eeece1; text-align:right; ">93</td><td style="background-color:#eeece1; text-align:right; ">22</td><td style="background-color:#eeece1; text-align:right; ">5</td><td style="background-color:#eeece1; text-align:right; ">42</td><td style="background-color:#eeece1; text-align:right; ">15</td><td style="background-color:#eeece1; text-align:right; ">54</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="color:#ff0000; "> </td><td > </td><td style="background-color:#eeece1; text-align:right; ">64</td><td style="background-color:#eeece1; text-align:right; ">89</td><td style="background-color:#eeece1; text-align:right; ">15</td><td style="background-color:#eeece1; text-align:right; ">30</td><td style="color:#ff0000; background-color:#ffff00; font-style:italic; text-decoration:underline; text-align:right; ">71.000</td><td style="background-color:#eeece1; text-align:right; ">15</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td style="background-color:#eeece1; text-align:right; ">100</td><td style="background-color:#eeece1; text-align:right; ">24</td><td style="background-color:#eeece1; text-align:right; ">91</td><td style="background-color:#eeece1; text-align:right; ">29</td><td style="background-color:#eeece1; text-align:right; ">48</td><td style="background-color:#eeece1; text-align:right; ">98</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td style="color:#ff0000; background-color:#ffff00; font-style:italic; text-decoration:underline; text-align:right; ">67.000</td><td style="background-color:#eeece1; text-align:right; ">20</td><td style="background-color:#eeece1; text-align:right; ">41</td><td style="color:#ff0000; background-color:#ffff00; font-style:italic; text-decoration:underline; text-align:right; ">79.000</td><td style="background-color:#eeece1; text-align:right; ">20</td><td style="color:#ff0000; background-color:#ffff00; font-style:italic; text-decoration:underline; text-align:right; ">76.000</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-style:italic; "> </td><td > </td><td style="background-color:#eeece1; text-align:right; ">46</td><td style="background-color:#eeece1; text-align:right; ">90</td><td style="background-color:#eeece1; text-align:right; ">65</td><td style="background-color:#eeece1; text-align:right; ">51</td><td style="background-color:#eeece1; text-align:right; ">92</td><td style="background-color:#eeece1; text-align:right; ">31</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td style="background-color:#eeece1; text-align:right; ">20</td><td style="color:#ff0000; background-color:#ffff00; font-style:italic; text-decoration:underline; text-align:right; ">87.000</td><td style="background-color:#eeece1; text-align:right; ">96</td><td style="background-color:#eeece1; text-align:right; ">94</td><td style="background-color:#eeece1; text-align:right; ">53</td><td style="background-color:#eeece1; text-align:right; ">39</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td style="color:#ff0000; background-color:#ffff00; font-style:italic; text-decoration:underline; text-align:right; ">79.000</td><td style="background-color:#eeece1; text-align:right; ">45</td><td style="background-color:#eeece1; text-align:right; ">100</td><td style="background-color:#eeece1; text-align:right; ">59</td><td style="background-color:#eeece1; text-align:right; ">15</td><td style="background-color:#eeece1; text-align:right; ">55</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; background-color:#fffcf9; border-style: groove ;border-color:#ff0000"><tr><td ><b>Conditional formatting </b></td></tr><tr><td ><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial; font-size:10pt; padding-left:2pt; padding-right:2pt; "><tr><td >Cell</td><td >Nr.: / Condition</td><td >Format</td></tr><tr><td >C1</td><td >1. / Specific Text</td><td style="font-family:Calibri; color:#ff0000; font-weight:bold; font-style:italic; text-decoration:underline; background-color:#ffff00; ">Abc</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4


The code below is triggered after you have the Cell A1 selected and
then click on any other cell. (after changing its formatting).

Option Explicit
 
Public blCF_Flag As Boolean
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rngExample As Range
    If blCF_Flag Then 'last selection was CF Example Cell A1
         On Error GoTo CleanUp
         Set rngExample = Range("A1")
         Application.EnableEvents = False
         With Range("CFRange").FormatConditions(1)
             With .Font
                .Bold = rngExample.Font.Bold
                .ColorIndex = rngExample.Font.ColorIndex
                .Color = rngExample.Font.Color
                .Italic = rngExample.Font.Italic
                .Strikethrough = rngExample.Font.Strikethrough
                .ThemeFont = rngExample.Font.ThemeFont
                .TintAndShade = rngExample.Font.TintAndShade
                .Underline = rngExample.Font.Underline
             End With
             With .Interior
                .Color = rngExample.Interior.Color
                .ColorIndex = rngExample.Interior.ColorIndex
                .Pattern = rngExample.Interior.Pattern
                .PatternColor = rngExample.Interior.PatternColor
                .PatternColorIndex = rngExample.Interior.PatternColorIndex
                .PatternTintAndShade = _
                        rngExample.Interior.PatternTintAndShade
                .TintAndShade = rngExample.Interior.TintAndShade
             End With
             .NumberFormat = rngExample.NumberFormat
         End With
         blCF_Flag = False
    Else
        If Not (Intersect(Target, Range("A1")) Is Nothing And _
            Target.Count = 1) Then blCF_Flag = True
    End If
CleanUp:
    Application.EnableEvents = True
    Set rngExample = Nothing
End Sub

Be aware that unexpected results could occur if all the cells within your range
with conditional formatting don't have the same number and order of rules.
IE: The first rule in Cell C5 is the second rule in Cell D5.
More error handling would need to be added to this to make this more robust for
those cases.

Also, this code doesn't attempt to transfer borders, gradients or themes...
those would take a bit more work to set up.

 

[Draperies]

A bit more complicated than I had hoped but that looks like it will do the trick! Thank you very much!