Change font to Italic

[DavidH56]

Hi,

Could anyone please let me know how would I programmically to change all
cells with red fonts to italics and underline them from cell A2 through Q
with variable rows?

Thanks in advance.

 

[Mike H]

David,

I didn't understand the range you wanted to work on

cells with red fonts to italics and underline them from cell A2 through Q

so this has a line that will work for A2 - Q100 and another that will work
on the entire used range so use which you prefer.

Right click the sheet tab, view code and paste this in.

Sub Prime_Lending()
ActiveSheet.Range("A2:Q100").SpecialCells(xlCellTypeConstants,
xlTextValues).Select
'ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues).Select
For Each c In Selection
If c.Font.ColorIndex = 3 Then
With c.Font
.FontStyle = "Italic"
.Underline = xlUnderlineStyleSingle
End With
End If
Next
End Sub


Mike

 

[DavidH56]

Thanks Mike for such a fast response,

My mistake, I meant used range because row amounts change each week. Is
there a way to run this in a macro so for my weekly the final report?

Thanks again

 

[Mike H]

David,

I assume you have pasted the macro into the worksheet code module as
indicated below. If you have there are a couple of ways to run it.

Tools|Macro|Select this workbook|Highlight the macro name|Run

or

View|Toolbars|Forms
Click the 'Button' icon
Hover your cursor on the worksheet where you want the button
Left click and drag a button onto the worksheet
In the popup window assign the macro
Click the button

Mike

 

[DavidH56]

Mike,

I keep getting an error variable not defined in this line:
If c.Font.ColorIndex = 3 Then

 

[Mike H]

Add this as the first line

Dim c As Range


Mike

 

[DavidH56]

Mike,

Thanks so much for your help. I put in your statement and it ran, but I had
not realized that some of the rows were hidden and filtered, so it ran but
skipped over some of the visible columns. I modified it a little and it
worked. This is what I have:

Sub Prime_Lending()

Dim C As Range

Range("A1:Q1").Select
Range(Selection, Selection.End(xlDown)).Select
'ActiveSheet.Range("A2:Q2000").SpecialCells(xlCellTypeConstants, _
'xlTextValues).Select
'ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues).Select
For Each C In Selection
If C.Font.ColorIndex = 3 Then
With C.Font
.FontStyle = "Italic"
.Underline = xlUnderlineStyleSingle
End With
End If
Next
End Sub

This seems to work okay.

Thanks again for your help.

DH