Replace coloured cells with a value

[Richard Black]

Hi,

I've got 20 or so excel spreadsheets containg some timetable information.
It has been entered in a rather odd format that is preventing me analysing
it. There are no values, only differently coloured cells!

Ideally, I would like to be able to convert the coloured cells into cells
with values relating to their colour. I have some VBA experience and am
aware of the Font.ColorIndex and Interior.ColorIndex properties that can be
evaluated.

Does anyone have any ideas on a procedure to convert the cells within a
specified range from null values with colours to values with no colours?

TIA,

Richard

 

[Dave Peterson]

Something like:

Option Explicit
Sub testme01()

Dim myCell As Range

For Each myCell In Selection.Cells
If IsEmpty(myCell) Then
If myCell.Interior.ColorIndex = xlNone Then
'do nothing
Else
myCell.Value = myCell.Interior.ColorIndex
End If
End If
Next myCell

End Sub

 

[Richard Black]

Wow, fast reply!

Thanks very much - worked great, although I modified it slightly to give
uncoloured cells a zero value and to erase any existing colours:

Sub testme01()

Dim myCell As Range

For Each myCell In Selection.Cells
If IsEmpty(myCell) Then
If myCell.Interior.ColorIndex = xlNone Then
'give cell 0 value
myCell.Value = "0"
Else
myCell.Value = myCell.Interior.ColorIndex
'erase cell colour
myCell.Interior.ColorIndex = xlColorIndexNone
End If
End If
Next myCell

End Sub

Thanks again,

Richard

 

[Dave Peterson]

Glad you got it working.

Sometimes, I like to do part of the stuff via the macro. Then check the output