Color filled cells

[KnAsTa]

G'day,

Can anyone field this question for me?

i want to know how to keep colour filled cells fixed in place. So, if u wanted to "sort" your spreadsheet, the filled cells would not move, only the data would move.

Thanx guys!

 

[Al Chara]

Try the following macros:

Public addresses(1000), colors(1000)
Sub SaveColors()
Dim i As Integer
For Each Cell In ActiveSheet.UsedRange
addresses(i) = Cell.Address
colors(i) = Cell.Interior.ColorIndex
i = i + 1
Next
End Sub
Sub UpdateColors()
ActiveSheet.UsedRange.ClearFormats
For i = 0 To 1000
On Error GoTo 1
Range(addresses(i)).Interior.ColorIndex = colors(i)
Next
1 End Sub

Run SaveColors before you sort and then run UpdateColors after you have sorted. The "1000" is just a number I chose to guess how many cells you have. You can increase or decrease as needed.

 

[KnAsTa]

Thanks Al, much appreciated!

 

[s-o-s]

Hi KnAsTa,

You can do this using conditional formatting.

If you want columns A-G in row 4 to be a blue background

Select the cells in A-G in row 4.
Format Conditional Formatting
In the 1st Drop Down change from Cell Value is to Formula is
In the next box enter:
=row()=row()

Next click the format box choose the patterns tab and select the colour of your choice.

Click OK
Click OK

This can now be sorted and will not move.

This formatting can be copied and since the condition is always true will show the colour of your choice.

 

[Chris Davison]

Good one s-o-s, I like this