show font color value for cells in Excel

[gliptak]

Hi Folks,
This question probably has a simple answer, but my knowledge of VB is extremely limited. If this can be solved formulaically in excel without writing a VB script, that would be preferable. Here is the problem:

I have an excel sheet with several thousand line items, some of which need to be removed. The font color on these has been changed to red to indicate this; however, there is no (column)indicator to separate these from the other line items, and the red ones are interspersed throughout the records.

If the font color for a particular cell can be revealed (i.e. showing the xlcolorindex font value for the cell (black=1, red=3, etc.)) then one can quickly sort on the index value to group these by color.

Is this possible to solve by using a formula, or is vbasic the only recourse? What is the simplest way to do this?

Thanks for your help.

Regards,

GL

[gwdash]

Add this code to your VBA window:

Sub ShowColor()
Dim x As Worksheet 'worksheet var
Dim c As Range 'cell range
Set x = ActiveSheet 'set worksheet var
For Each c In x.Range("A:A") 'for each cell in column 1
If c.Value <> "" Then 'if not empty
If c.Font.ColorIndex <> -4105 Then          'if color is
x.Cells(c.Row, 2).Value = c.Font.ColorIndex 'automatic then
Else                                        'show 1, else
x.Cells(c.Row, 2).Value = 1                 'show value
End If
End If
Next ' loop
End Sub

Change the Column "A:A" to the correct coumn, "B:B" for B, etc.

Also change the 2's to whatever you want the column for data to be but in.

if you want it to be put into the next column, change both of them to this

x.Cells(c.Row, c.Column + 1).Value = '...

I just whipped this up and tested it, it should work. Just call it where ever(a button or something)

[gliptak]

Thanks gwdash,
I implemented your solution; it worked perfectly. We were able to eliminate over 6000 records this way. Where can we mail the consulting fees?

Regards,

gliptak

[gwdash]

LOL