Issues with Conditional Formatting Across Workbooks

Question

I have a very simple table that I applied a conditional formatting rule on. The rule states to highlight values less than a certain amount a particular color. However, I find that when I copy the worksheet over to another workbook the color of the highlighted cells changes dramatically. In this instance it changed from shades of green to shades of orange. Is this a common problem? Does anyone happen to know how to fix this?

Tony Dallimore Tony Dallimore · Accepted Answer · 2014-06-27T20:38:18

I am unable to duplicate this issue.

Please add the code below to the problem workbooks.

Position the cursor over a cell that displays as a shade of green in the source workbook and run macro TestCF(). Then position the cursor over the same cell in the destination workbook and again run macro TestCF().

The macro displays the font colour set by the conditional formatting. I get the same output for both workbooks. Perhaps you will get different output.

Option Explicit
Sub TestCF()

  Dim InxCF As Long

  With ActiveCell
    Debug.Print "Workbook " & ActiveWorkbook.Name & "  Worksheet " & _
                ActiveSheet.Name & "  Cell " & ColNumToCode(ActiveCell.Column) & ActiveCell.Row
    For InxCF = 1 To .FormatConditions.Count
      Debug.Print "  Format " & InxCF
      With .FormatConditions(InxCF)
        Debug.Print "    Font colour: " & ColourToRGB(.Font.Color)
      End With
    Next
  End With

End Sub
Function ColourToRGB(ByVal Colour As Long) As String

  Dim Red As Long
  Dim Blue As Long
  Dim Green As Long
  Dim BlueGreen As Long

  Red = Colour Mod 256
  BlueGreen = Colour \ 256
  Green = BlueGreen Mod 256
  Blue = BlueGreen \ 256

  ColourToRGB = "RGB(" & Red & ", " & Green & ", " & Blue & ")"

End Function
Function ColNumToCode(ByVal ColNum As Long) As String

  Dim Code As String
  Dim PartNum As Long

  ' Last updated 3 Feb 12.  Adapted to handle three character codes.
  If ColNum = 0 Then
    ColNumToCode = "0"
  Else
    Code = ""
    Do While ColNum > 0
      PartNum = (ColNum - 1) Mod 26
      Code = Chr(65 + PartNum) & Code
      ColNum = (ColNum - PartNum - 1) \ 26
    Loop
  End If

  ColNumToCode = Code

End Function

Issues with Conditional Formatting Across Workbooks

1 Answers