change the color of the font & borders

ermccarthy

Board Regular
Joined
Feb 15, 2002
Messages
224
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!!!!!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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!!!
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top