Function that determines formatting of cells
Function that determines formatting of cells
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Function that determines formatting of cells

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Austin, TX
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Specifically which color a cell is shaded?

    Tried the CELL function, but didn't seem to have what i needed.

    any help would be appreciated.

    thanks

  2. #2
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try Conditional Formatting (Format-Conditional Formatting). Reply if you need more help.

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Austin, TX
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Conditional formatting is backwards from what i need. I don't need to format a cell based on it's contents, i need to know how a cell is formatted. Perhaps there's more to conditional formatting than i know, but I don't think this can help me. Example, i have a spread with some data that is shaded gray, which represents 'changed' data. I want to sort by the 'changed' data, but i don't have a column to sort by unless i can get something in the cells to determine if they are shaded or not. Any help?? Thanks

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Austin, TX
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Anyone have a clue with this...or should i just adapt the code that was suggested to jimi143 to suit my needs??

    Thanks

  5. #5
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you want to sort by a cell's interior shade (in your case, color Gray, Excel color index #15), maybe this macro can help. It assumes your data to be in column A, starting in A2, and that column B is free for evaluation and sorting purposes. Modify for color index number, data column, and evaluation ranges. Be aware that this code sorts just on one color (Gray), as you said. Other shaded (and non-shaded) cells in column A will all move down in order, starting with the first non-gray cell. If I misunderstood your question, sorry, please re-post.

    ''''''''''''''''''''''''''''''''''

    Sub SortGray()

    Dim rC As Range
    Dim rS As Range
    Dim cell As Range
    Set rC = Range([A2], [A65536].End(xlUp))
    Set rS = Range([A2], [A65536].End(xlUp).Offset(, 1))

    rC.Offset(, 1).Clear

    For Each cell In rC
    If cell.Interior.ColorIndex = 15 Then
    cell.Offset(, 1) = 1
    End If
    Next

    rS.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    rC.Offset(, 1).Clear
    [A1].Select

    End Sub

    ''''''''''''''''''''''''''''''''''''

    Any help?

    Tom Urtis

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Austin, TX
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Yeah, Tom, that helps, I ended up looping the following to get what I wanted since I don't often have to repeat this task, but I really wanted a way to do it in Excel for reasons you won't care about. Thx

    Dim shade As Integer
    shade = Selection.Interior.ColorIndex
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = shade
    ActiveCell.Offset(1, -1).Select

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