VBA formula for Excel 2003 to paste special values based on font color

0
votes

I am trying to use VBA to create a macro, that when called, will scan all the populated cells in the sheet and if the cells font color is purple (13), it will copy that cell, and special paste back just the number and turn the font color black. I have experimented a bit with it, but I'm in over my head, so if you have any recommended pages to learn vba I'd appreciate that too.

1
excelvba

1 Answers

0
votes

You can do almost all of this just by doing things and recording them with the macro recorder and seeing the results. This is not how you ought to do this, but is a simple example

Sub setPurpleValues()
 Dim myStr As String
 For Each cell In Range("A2:Z100")
  If cell.Font.ColorIndex = 13 Then myStr = cell.Value Else myStr = "No"
'MsgBox (myStr)
  If myStr <> "No" Then
   cell.Value = myStr
   cell.Font.ColorIndex = 1
  End If
 Next cell
End Sub