Color filled cells
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Color filled cells

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Australia
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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!

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try the following macros:

    Code:
    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.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Australia
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Al, much appreciated!

  4. #4
    Board Regular s-o-s's Avatar
    Join Date
    Apr 2002
    Location
    Kissimmee, Florida
    Posts
    384
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Good one s-o-s, I like this

User Tag List

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