none
VBA to set conditional formatting font color/bold & borders in Excel file, also turning off grid lines RRS feed

  • Question

  • I've look all over for code examples of setting font colors, font bold and borders (i.e. xlTop) in formatconditions from Access vba to Excel sheet.

    As an example, here is the code I have with attempts to set fontcolor.  Obviously it doesn't work.

                With .Range("A:V").FormatConditions
                    .Add Type:=2, Formula1:=StrSearchCriteria
    'Red with white font
    '                .fontcolor = lngWhite
                    With .Item(3)
                        .SetFirstPriority
    '                    .fontcolor = lngWhite
                        With .Interior
                            .PatternColorIndex = xlAutomatic
                            .ThemeColor = xlThemeColorAccent6
                            .TintAndShade = -0.249946592608417
                        End With
                        .StopIfTrue = False
                    End With
                End With

    I also can't figure out how to turn off grid lines on a sheet.

    I'm stumped.  Any help is greatly appreciated.


    Larry

    Wednesday, April 25, 2018 4:43 PM

All replies

  • Instead of .fontcolor, use .Font.Color

    To turn off gridlines, you can use

    ActiveWindow.DisplayGridlines = False


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

    Wednesday, April 25, 2018 5:13 PM
  • Thank you Hans.

    I tried the .Font.Color within the .Range and the .item(3) and both failed with "Application-defined or object-defined error."

    The grid lines off did work - thanks again.


    Larry

    Wednesday, April 25, 2018 5:27 PM
  • Try this:

                With .Range("A:V").FormatConditions.Add(Type:=xlExpression, Formula1:=StrSearchCriteria)
                    .SetFirstPriority
                    .Font.Color = lngWhite
                    With .Interior
                        .PatternColorIndex = xlAutomatic
                        .ThemeColor = xlThemeColorAccent6
                        .TintAndShade = -0.249946592608417
                    End With
                    .StopIfTrue = False
                End With


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

    Wednesday, April 25, 2018 6:45 PM
  • Thanks again, Hans.  Yes, that is one of the two tests that I tried which failed.

    Larry

    Wednesday, April 25, 2018 7:33 PM
  • You haven't told us what StrSearchCriteria is. When I tried it with a simple formula

        StrSearchCriteria = "=$A1>2"

    it worked correctly:


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

    Wednesday, April 25, 2018 7:48 PM
  •             StrSearchCriteria = "=($K1=" & Chr(34) & "Closed" & Chr(34) & ")"

    Sorry, the statement immediately before the code I previously posted.


    Larry

    Wednesday, April 25, 2018 8:26 PM
  • That looks OK. In what way does the code fail?

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

    Wednesday, April 25, 2018 8:35 PM
  • Hello leboyd,

    I tried Hans's code with your StrSearchCriteria, it works for me too.What error message did you get? Which line give you the error? 

    Besides, I would also suggest you try to set the condition format manually and record a macro and adjust the recorded macro for your need.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, April 26, 2018 8:14 AM