none
Excel VBA: Macro does not always show the value of .NumberFormat RRS feed

  • Question

  • I have created a macro that shows some of the format conditions of a selection.(see below).

    The macro is fine, only the ".NumberFormat" does not show
    if multiple values are set, such as "Borders" and "Interior".This while a value for  ".NumberFormat" has been entered.

    Do I do something wrong?


    Can anyone help me?

    The macro:

    Sub ListFormatConditions()

        'DEZE MACRO IS GEMAAKT OP 20 JULI 2017 DOOR RÜ
        'HET TOONT DE VOORWAARDELIJKE OPMAAKREGELS VAN EEN SELECTIE
        
        Set FC = Selection.FormatConditions
        Aantal = FC.Count
        
        If Aantal = 0 Then
            MsgBox "Binnen deze selectie is geen voorwaardelijke opmaak ingesteld."
            Exit Sub
        End If
        
        For Teller = 1 To Aantal
             
                MsgBoxString = Teller & "e regel voorwaardelijke opmaak (algemene priority = " & FC(Teller).Priority & ") :" & Chr(13) & Chr(13) & _
                    "Voor : """ & FC(Teller).Formula1 & """ en type = " & FC(Teller).Type & Chr(13) & _
                    "binnen range(s) """ & FC(Teller).AppliesTo.Address & """ geldt:" & Chr(13) & Chr(13)
                
                MsgBoxString = MsgBoxString & "Format cellwaarde: " & FC(Teller).NumberFormat & Chr(13)
                
                With FC(Teller).Font
                    MsgBoxString = MsgBoxString & "Opmaak: " & _
                         "| Bold = " & .Bold & " | Italic = " & .Italic & " | Color = " & .Color & " | TintAndShade = " & .TintAndShade & Chr(13) & Chr(13)
                End With
                
                With FC(Teller).Borders(xlLeft)
                    MsgBoxString = MsgBoxString & "Border left: " & _
                         "| LineStyle " & .LineStyle & " | Color = " & .Color & " | TintAndShade = " & .TintAndShade & " | Weight = " & .Weight & Chr(13)
                End With
                With FC(Teller).Borders(xlRight)
                    MsgBoxString = MsgBoxString & "idem right: " & _
                         "| LineStyle " & .LineStyle & " | Color = " & .Color & " | TintAndShade = " & .TintAndShade & " | Weight = " & .Weight & Chr(13)
                End With
                With FC(Teller).Borders(xlTop)
                    MsgBoxString = MsgBoxString & "idem top: " & _
                         "| LineStyle " & .LineStyle & " | Color = " & .Color & " | TintAndShade = " & .TintAndShade & " | Weight = " & .Weight & Chr(13)
                End With
                With FC(Teller).Borders(xlBottom)
                    MsgBoxString = MsgBoxString & "idem bottom: " & _
                         "| LineStyle " & .LineStyle & " | Color = " & .Color & " | TintAndShade = " & .TintAndShade & " | Weight = " & .Weight & Chr(13) & Chr(13)
                End With
        
                With FC(Teller).Interior
                    MsgBoxString = MsgBoxString & "Interior: " & _
                         "| Color " & .Color & " | Pattern = " & .Pattern & " | PatternColor = " & .PatternColor & Chr(13) & " | PatternTintAndShade = " & .PatternTintAndShade & " | TintAndShade = " & .TintAndShade
                End With
               
                MsgBox MsgBoxString
        
        Next Teller
        
    End Sub
    Monday, July 24, 2017 2:13 PM

All replies

  • The macro shows number format even if I have also set font, border and interior:


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Monday, July 24, 2017 8:30 PM
  • Hi Rü,

    I got the same result as Hans, .NumberFormat could be shown by the macro.

    Did you get any error? Have you tested the function in different workbooks?

    Whats the version and building number of you Excel?

    I suggest you share your steps about how to set the format conditions or you could share us your excel workbook so we could try to reproduce your issue.

    Best Regards,

    Terry

    Wednesday, July 26, 2017 9:23 AM
  • The macro shows number format even if I have also set font, border and interior:


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Thank you Hans for your response!
    It's true, I've tried it myself too and in this case it's also good.

    But I initially used a macro (see bottom of message) from some forum and that uses format conditions.
    If you apply it and
    - then the area selected (N1: N29)
    - you selected in the "conditional format" menu
    - you add to the first format condition, for example, fill the cell with yellow (interior).
    - You are saving it
    - and after that, my macro did not show the number format anymore (first line).

    What's wrong then?
    Has something to do with the language version of excel?

    For @Terry: my version of Excel is 2016, Dutch version, version 1706, build number 8229.2073

    Here is the macro:

    Sub SetConditFormatN()  

    'Ensure that conditional formatting is
    'cleared from entire range to be
    'conditionally formatted.
    With Sheets("List format conditions").Range("N1:N29").FormatConditions.Delete
    End With

    'Set conditional format for one cell
    With Sheets("List format conditions").Range("N1")

    .FormatConditions.Add Type:=xlExpression, _
    Formula1:="=N1<=5"

    .FormatConditions(1).NumberFormat _
    = "$#,##0.00"
    .FormatConditions(1).StopIfTrue = True

    .FormatConditions.Add Type:=xlExpression, _
    Formula1:="=N1<=10"

    .FormatConditions(2).NumberFormat _
    = "0.00"
    .FormatConditions(2).StopIfTrue = True

    .FormatConditions.Add Type:=xlExpression, _
    Formula1:="=N1<=15"

    .FormatConditions(3).NumberFormat _
    = "0.000"

    .FormatConditions(3).StopIfTrue _
    = True

    .FormatConditions.Add Type:=xlExpression, _
    Formula1:="=N1<=20"

    .FormatConditions(4).NumberFormat _
    = "0.0000"

    .FormatConditions(4).StopIfTrue = True

    .FormatConditions.Add Type:=xlExpression, _
    Formula1:="=N120"

    .FormatConditions(5).NumberFormat _
    = "0.000000"

    .FormatConditions(5).StopIfTrue = True

    End With

    'Copy conditional format to other cells
    With Sheets("List format conditions")

    .Range("N1").Copy

    'Include the copied cell in the Paste range
    .Range("N1:N29").PasteSpecial _
    Paste:=xlPasteFormats
    End With

    End Sub


    • Edited by Wednesday, July 26, 2017 6:21 PM
    Wednesday, July 26, 2017 6:17 PM
  • Thanks. I can reproduce the problem using your description. It's weird! The Conditional Formatting > Manage Rules > Edit Rule > Format... dialog still lists the correct number format, but the macro doesn't.

    If I edit the rule again and specify a number format, it IS listed by the macro.

    You could use File > Feedback to report the bug (I will do so too).


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, July 26, 2017 7:17 PM
  • Hi Rü,

    ->you add to the first format condition, for example, fill the cell with yellow (interior)

    I still failed to reproduce your issue. I suggest you do this step without applying the SetConditFormatN macro, could ".NumberFormat"  be shown? I have done a test, it could show the .NumberFormat.

    I’m using Version 1706, build number 8229.2103. I have also reverted to 8229.2073. It still work.

    Here  is my testing, did i misunderstand something?


    Best Regards,

    Terry


    Thursday, July 27, 2017 11:03 AM
  • See Hans Vogelaar's reaction.

    As I wrote before, I think it has something to do with the language version of Excel. You probably have the English version, we the Dutch.


    Thursday, July 27, 2017 6:12 PM
  • I use the English language version of Excel, on an English language version of Windows, and with English system settings, so I don't think it's language dependent.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, July 27, 2017 8:22 PM
  • It's becoming more mysterious Hans.

    I have reported the bug to Microsoft.

    @Terry, can I ask you something else, with what tool did you make the .gif file?

    Thursday, July 27, 2017 8:53 PM
  • Hi Rü,

    You are welcome, the tool is called LICEcap. 

    Best Regards,

    Terry

    Friday, July 28, 2017 9:53 AM
  • Hi Rü,

    You are welcome, the tool is called LICEcap. 

    Best Regards,

    Terry

    @Terry, thank you for your response!
    The progamma LiceCap works fantastic.
    Here's a GIF that i have made of the phenomenon ...


    Friday, July 28, 2017 3:03 PM
  • Hi Rü,

    I could reproduce your issue according to your gif demonstrate now. The issue doesn't depends on language version.

    Best Regards,

    Terry

    Tuesday, August 1, 2017 8:25 AM