border colors and style

[ralf]

I have different border colors in my sheet and i would like to make them all
the same. how can i do that without redrawing them all?
the same with color fill of a cell.

 

[מיכ×ל (מיקי) ×בידן]

Suppose you like all EXISTING borders to be REd - select the whole range [NOT
the whole sheet] and run this Macro:
==============
Sub Micky()
For Each CL In Selection
If CL.Borders.LineStyle > 0 Then CL.Borders.ColorIndex = 3
Next
End Sub
==========
Micky

 

[Gord Dibben]

David's suggestion will give you background color and borders on every cell
in your worksheet.

Do you just want to change any existing colors and borders?

This macro will re-color only currently colored cells in the usedrange.

Sub colors()
For Each cell In ActiveSheet.UsedRange
With cell
If .Interior.ColorIndex <> -4142 _
And .Interior.ColorIndex <> 6 Then
.Interior.ColorIndex = 6
End If
End With
Next
End Sub

You would have to do same for cells with existing borders.

Are the current borders' styles the same or different?

You can probably do an Edit>Replace using cell formats.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

I like that Micky


Gord

Suppose you like all EXISTING borders to be REd - select the whole range [NOT
the whole sheet] and run this Macro:
==============
Sub Micky()
For Each CL In Selection
If CL.Borders.LineStyle > 0 Then CL.Borders.ColorIndex = 3
Next
End Sub
==========
Micky

I have different border colors in my sheet and i would like to make them all
the same. how can i do that without redrawing them all?
the same with color fill of a cell.

 

[מיכ×ל (מיקי) ×בידן]

Btw - as far as I remember, UsedRange applies to cells with data and/or had
some data, in the past, AND NOT to empty cells just being "bordered".
Am I mistaken !?
Micky

 

[Dave Peterson]

It depends on how the border was applied.

If I apply a border to a single cell (outside the current used range), then the
usedrange adjusts.

If I apply a border to a complete row/column, then only the first cell in the
row/column is used to adjust the range (so the usedrange could change).

 

[Gord Dibben]

UsedRange includes all cells, blank or not.

Can also include cells which had data that was cleared.

UsedRange can be larger than the real range but in this case I did not think
that was relevant.

Open a new sheet and place borders around all cells in A1:G34.........no
data entry, just borders.

Then run this revision of your macro.

Sub Micky()
For Each CL In ActiveSheet.UsedRange
If CL.Borders.LineStyle > 0 Then CL.Borders.ColorIndex = 3
Next
End Sub


Gord