Need a macro to change font in unique cells

[mike575]

Is it possible to create a macro that will change the font size from exisiting10 pt to 12 pt and then Italic the cell data if the number in the cell equal 25. The number of cell would involve 4 columns and 100 lines in each column.(h9:k108) The cell are used to record a number of target hit out of 25 possible. When the results are printed the perfect scores would have more prominance. Currently we are cutting and pasting the cell with 25 but this is a pain. Could some one give me idea how to construct the macro.

[Jacob Hilderbrand]

Try this macro.



Sub ReFormatCells()
Dim Rng As Range
Dim Cel As Range
Dim FirstAddress As String
Set Rng = Range("H9:K108")
With Rng
.Font.Italic = False
.Font.Size = 10
Set Cel = .Find(What:="25", LookIn:=xlValues, LookAt:=xlWhole)
If Not Cel Is Nothing Then
FirstAddress = Cel.Address
Do
Cel.Font.Italic = True
Cel.Font.Size = 12
Set Cel = .FindNext(Cel)
Loop While Not Cel Is Nothing And Cel.Address <> FirstAddress
End If
End With
End Sub

[mike575]

Thank you very much it works. I'll give it a work out sunday at the trap shoot. I am glad you put the portion about setting the font size to 10 point before changing the 25 to 12 point. I had to use the initial part in a seperate routine to restore the original font to 10 point in different worksheet. Now I got to learn what all the commands mean.


Thanks so much

[Jacob Hilderbrand]

You're Welcome :beerchug:

Take Care

[geekgirlau]

Mike, what about using conditional formatting here? Set the first cell to 10pt, add a conditional format to change it to 12pt and italic if the cell value = 25, then copy and paste the format only over the entire range.

[Jacob Hilderbrand]

Unfortunately you cannot change the font size with Conditional Formatting.

[geekgirlau]

Jake you are 100% correct - I stand corrected :bow: