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.

Select the entire sheet by clicking the top left-hand corner box (above row
1 and to the left of column A), then set your desired border and fill
colour.
--
David Biddulph


ralf wrote:
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


"ralf" wrote:

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.

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 EditReplace using cell formats.


Gord Dibben MS Excel MVP



On Sat, 16 Jan 2010 07:28:01 -0800, ralf
wrote:

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.

I like that Micky


Gord

On Sat, 16 Jan 2010 09:05:01 -0800, ????? (????) ?????
<micky-a*at*tapuz.co.il wrote:

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


"ralf" wrote:

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


"Gord Dibben" wrote:

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 EditReplace using cell formats.


Gord Dibben MS Excel MVP



On Sat, 16 Jan 2010 07:28:01 -0800, ralf
wrote:

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.

.

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).



????? (????) ????? wrote:

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

"Gord Dibben" wrote:

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 EditReplace using cell formats.


Gord Dibben MS Excel MVP



On Sat, 16 Jan 2010 07:28:01 -0800, ralf
wrote:

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.

.


--

Dave Peterson

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


On Sat, 16 Jan 2010 23:54:01 -0800, ????? (????) ?????
<micky-a*at*tapuz.co.il wrote:

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


"Gord Dibben" wrote:

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 EditReplace using cell formats.


Gord Dibben MS Excel MVP



On Sat, 16 Jan 2010 07:28:01 -0800, ralf
wrote:

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.

.