cell properties sniffer?

[jayd4wg]

has anyone ever written a cell properties sniffer?

I'm trying to do a simple text and background color inverter, and when i go to white background with black text, i lose the fine gray outline. But later on, i'll be doing much more with it and it would be very helpful to know what the VBA properties are for certain cells.

[chead5]

Interesting question. Worth a bit of distraction I think. I'll get back to you if I find something

[jayd4wg]

i'm envisioning a msgBox containing the properties of a clicked cell. THAT would be ideal

[erik.van.geit]

jayd4wg,

can you use this

Sub color_inverter()
'Erik Van Geit
'050607

Dim CC As Long
Dim FC As Long
Dim cell As Range

Application.ScreenUpdating = False

For Each cell In Selection
CC = cell.Interior.Color
FC = cell.Font.Color
cell.Interior.Color = FC
cell.Font.Color = CC
Next cell

Application.ScreenUpdating = False
End Sub

to display properties

For Each cell In Selection
msg = "Borders.Color" & vbTab & cell.Borders.Color & Chr(10) & _
"CurrentRegion" & vbTab & cell.CurrentRegion.Address(0, 0) & Chr(10) & _
"Formula" & vbTab & vbTab & cell.Formula
MsgBox msg, 64, "cell" & cell.Address(0, 0)
Next cell

typing cell and then a dot will give you a list of properties and instructions ... choose what you want

kind regards,
Erik

[jayd4wg]

Thanks yet again Erik!

[chead5]

I there a way to loop thru all of the properties of a cell and list their states?

[erik.van.geit]

would be intresting ...

it exists for workbooks
something like this
http://www.mrexcel.com/board2/viewtopic.php?t=111162

Sub shProps() 
Dim itemN, myItem, myList 

On Error Resume Next 
For Each p In ActiveWorkbook.BuiltinDocumentProperties 
itemN = p.Name 
myItem = p.Value 
myList = myList & vbCr & itemN & ": " & myItem 
Next 
MsgBox myList 
End Sub

kind regards,
Erik

[jayd4wg]

Originally Posted by chead5

I there a way to loop thru all of the properties of a cell and list their states?

This is how I came upon the correct colorIndex for the sheet I was working on:

Sub cellOutlines()
'Written by Jason Basham
'06-07-05
'Description - This cycles thru all available color index numbers, and 
'places an outline around the corresponding rows in column A
'ie. a1 is ColorIndex=1 and a28 is ColorIndex=28

Dim i As Integer
i = 1

    Do While i < 57'loop 57 times, the maximum ColorIndex value
            
            ActiveCell.Borders.LineStyle = xlContinuous
            ActiveCell.Borders.Weight = xlThin ' set to xlThick, will be easier to see
            ActiveCell.Borders.ColorIndex = i
            ActiveCell.Offset(1, 0).Select 'go to next cell down
            i = i + 1
    Loop

 End Sub

[erik.van.geit]

jayd4wg,

I think chead5's question was meant to display all kind of properties...
Any takers for that part ?

kind regards,
Erik

[jayd4wg]

Originally Posted by erik.van.geit

jayd4wg,

I think chead5's question was meant to display all kind of properties...
Any takers for that part ?

kind regards,
Erik

I was hoping to start some wheels turning in his head with what I posted. but then I thought...a single spreadsheet with all different properties might be kind of tedious...much more so than a sniffer to check the properties of an individual cell IMO. I'm not up to the task, at least not right now, my plate is FULL.