Function that determines formatting of cells

[msvec]

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

 

[Russell Hauf]

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

 

[msvec]

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

 

[msvec]

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

Thanks

 

[Tom Urtis]

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

 

[msvec]

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