Results 1 to 7 of 7

change the color of the font & borders

This is a discussion on change the color of the font & borders within the Excel Questions forums, part of the Question Forums category; Ok, I am trying to do a work around. I want a macro that selects an area, I want the ...

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    San Antonio, TX
    Posts
    189

    Default change the color of the font & borders

    Ok, I am trying to do a work around. I want a macro that selects an area, I want the macro to change any font that is not the default (black, color index 0) to change it to white (color index 2) so it doesn't show up when it prints. Then at the end of the macro I will change it back all the white fonts to a different color.......Below is the simple macro that I wrote, but doesn't seem to work......perhaps some of you gurus can help me out...


    Range("B3:G11").Select
    If Selection.Font.ColorIndex <> 0 Then

    With Selection.Font
    .ColorIndex = 2
    End With
    End If

    End Sub


    Now there is one more piece of the puzzle that I am not sure where to even start. Same thing as above, but I want to also change any borders that are not the default color to white also, so they do not show up when printed either.......

    PLEASE HELP!!!!!

  2. #2
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: change the color of the font & borders

    Hi ermccarthy:

    In reference to first part of your question, try ...
    Code:
    Sub yChangeCellsWithNon_DefaultFontToWhiteColor1()
        For Each cell In [B3:G11]
            If cell.Font.ColorIndex > 0 Then cell.Font.ColorIndex = 2
        Next cell
    End Sub
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    San Antonio, TX
    Posts
    189

    Default Re: change the color of the font & borders

    You are the greatest........got that working, Anyone have luck on the Borders??

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: change the color of the font & borders

    And for the second part, if I have understood you correctly, please try ...
    Code:
    Sub yClearBoderIfCellDefaultFontColorIdNotDefault1()
        For Each cell In [B3:G11]
            If cell.Font.ColorIndex > 0 Then cell.Borders.LineStyle = 0
        Next cell
    End Sub
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    San Antonio, TX
    Posts
    189

    Default Re: change the color of the font & borders

    Not quite, but I have been playing with it, but can't get it to want I want........basically everything in the area that is not the default color of 0 (black) I want it to change to white (2) ....font & borders, even if the borders are around a cell that contains black font, as long as the border is not black.

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: change the color of the font & borders

    Hi ermccarthy:

    If I have understood you correctly, the following should do it ...
    Code:
    Sub yClearBoderIfBorderColorIsNotBlack1()
        For Each cell In [B3:G11]
            'If cell.Font.ColorIndex > 0 Then cell.Borders.LineStyle = 0
            If cell.Borders(xlEdgeTop).ColorIndex > 0 Then cell.Borders.LineStyle = 0
            If cell.Borders(xlEdgeBottom).ColorIndex > 0 Then cell.Borders.LineStyle = 0
            If cell.Borders(xlEdgeRight).ColorIndex > 0 Then cell.Borders.LineStyle = 0
            If cell.Borders(xlEdgeLeft).ColorIndex > 0 Then cell.Borders.LineStyle = 0
        Next cell
    End Sub
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    San Antonio, TX
    Posts
    189

    Default Re: change the color of the font & borders

    Close.....But I figured it out thanks to all the Help!!!!!

    Here was my final macro......

    test Macro
    '

    ' remove all non default font & borders

    Range("A1:I62").Select
    For Each cell In [A1:I62]
    If cell.Font.ColorIndex > 0 Then cell.Font.ColorIndex = 2
    Next cell
    For Each cell In [A1:I62]
    If cell.Borders.LineStyle > 0 Then cell.Borders.LineStyle = 2
    Next cell
    For Each cell In [A1:I62]
    'If cell.Font.ColorIndex > 0 Then cell.Borders.LineStyle = 0
    If cell.Borders(xlEdgeTop).ColorIndex > 0 Then cell.Borders.ColorIndex = 2
    If cell.Borders(xlEdgeBottom).ColorIndex > 0 Then cell.Borders.ColorIndex = 2
    If cell.Borders(xlEdgeRight).ColorIndex > 0 Then cell.Borders.ColorIndex = 2
    If cell.Borders(xlEdgeLeft).ColorIndex > 0 Then cell.Borders.ColorIndex = 2
    Next cell
    ' print the file (insert form in paper tray here)

    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

    ' reset all the now white font & borders to gray for next use

    Range("A1:I62").Select
    For Each cell In [A1:I62]
    If cell.Font.ColorIndex = 2 Then cell.Font.ColorIndex = 16
    Next cell
    For Each cell In [A1:I62]
    If cell.Borders.LineStyle = 2 Then cell.Borders.LineStyle = 16
    Next cell

    For Each cell In [A1:I62]
    'If cell.Font.ColorIndex > 0 Then cell.Borders.LineStyle = 0
    If cell.Borders(xlEdgeTop).ColorIndex = 2 Then cell.Borders.ColorIndex = 16
    If cell.Borders(xlEdgeBottom).ColorIndex = 2 Then cell.Borders.ColorIndex = 16
    If cell.Borders(xlEdgeRight).ColorIndex = 2 Then cell.Borders.ColorIndex = 16
    If cell.Borders(xlEdgeLeft).ColorIndex = 2 Then cell.Borders.ColorIndex = 16
    Next cell

    End Sub




    THANKS A Million!!!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com