Add cell formatting to Clear macro?

**dillinger** · 09-26-2010, 05:32 PM

First, thank everyone so much for all of your helpful tips and everything this site offers.

I am using the following UDF to clear the contents of a range of cells and would like to add the ability to change the text color of some of the cells back to black. Can this be done?

"Private Sub CLEAR_Click()

If MsgBox("Are you sure ?", vbYesNo + vbQuestion) = vbNo Then
Exit Sub
Else
Range("B5,b7,c7,b10:b12,b14:b16,b18:b20,c23,e3:e500,f3:f500,g3:g500,h3:h500,i3:i500,j3:j500,m2:m9,m14,m15,n5,l18:l23,m26,m28:m35,m39:m46,m50:m57,m61:m68,m72:m79,m83:m90,m94:m101,m105:m112,n31,n42,n53,n64,n75,n86,n97,n108").Select
Selection.ClearContents

End If

End Sub"

I want to change the following ranges back to black text: b10:b12,b14:b16,b18:b20 - c23 - e3:e500,f3:f500,g3:g500,h3:h500,i3:i500,j3:j500.

Any help would be greatly appreciated.

Adam

**pike** · 09-26-2010, 05:34 PM

Hi dillinger
Please take a few minutes to read the forum rules, and then add the code tags as required.

**dillinger** · 09-26-2010, 05:51 PM

Sorry! I have never posted a chunk of code before. First, thank everyone so much for all of your helpful tips and everything this site offers.

I am using the following UDF to clear the contents of a range of cells and would like to add the ability to change the text color of some of the cells back to black. Can this be done?

Private Sub CLEAR_Click()

If MsgBox("Are you sure ?", vbYesNo + vbQuestion) = vbNo Then
Exit Sub
Else
Range("B5,b7,c7,b10:b12,b14:b16,b18:b20,c23,e3:e500,f3:f500,g3:g500,h3:h500,i3:i500,j3:j500,m2:m9,m1 4,m15,n5,l18:l23,m26,m28:m35,m39:m46,m50:m57,m61:m68,m72:m79,m83:m90,m94:m101,m105:m112,n31,n42,n53, n64,n75,n86,n97,n108").Select
Selection.ClearContents

End If

End Sub

I want to change the following ranges back to black text: b10:b12,b14:b16,b18:b20 - c23 - e3:e500,f3:f500,g3:g500,h3:h500,i3:i500,j3:j500.

Any help would be greatly appreciated.

Adam

**Leith Ross** · 09-26-2010, 07:17 PM

Hello Adam,

Welcome to the Forum!

This will reset the cells to black font.

Private Sub CLEAR_Click()

  If MsgBox("Are you sure ?", vbYesNo + vbQuestion) = vbNo Then
     Exit Sub
  Else
     Range("B5,b7,c7,b10:b12,b14:b16,b18:b20,c23,e3:e500,f3:f500,g3:g500,h3:h500,i3:i500,j3:j500,m2:m9,m1 4,m15,n5,l18:l23,m26,m28:m35,m39:m46,m50:m57,m61:m68,m72:m79,m83:m90,m94:m101,m105:m112,n31,n42,n53, n64,n75,n86,n97,n108").ClearContents
  End If

  Range("b10:b12,b14:b16,b18:b20,c23,e3:e500,f3:f500,g3:g500,h3:h500,i3:i500,j3:j500").Font.ColorIndex = 1

End Sub