Change font to Italic

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DavidH56 - 16 May 2008 11:44 GMT

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.

Signature

By persisting in your path, though you forfeit the little, you gain the
great.

Reply to this Message

Mike H - 16 May 2008 12:13 GMT

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

> Hi,
>
[quoted text clipped - 3 lines]
>
> Thanks in advance.

Reply to this Message

DavidH56 - 16 May 2008 12:24 GMT

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

Signature

By persisting in your path, though you forfeit the little, you gain the
great.

> David,
>
[quoted text clipped - 28 lines]
> >
> > Thanks in advance.

Reply to this Message

Mike H - 16 May 2008 12:31 GMT

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

> Thanks Mike for such a fast response,
>
[quoted text clipped - 35 lines]
> > >
> > > Thanks in advance.

Reply to this Message

DavidH56 - 16 May 2008 13:22 GMT

Mike,

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

Signature

By persisting in your path, though you forfeit the little, you gain the
great.

> David,
>
[quoted text clipped - 53 lines]
> > > >
> > > > Thanks in advance.

Reply to this Message

Mike H - 16 May 2008 13:53 GMT

Add this as the first line

Dim c As Range

Mike

> Mike,
>
[quoted text clipped - 58 lines]
> > > > >
> > > > > Thanks in advance.

Reply to this Message

DavidH56 - 16 May 2008 14:17 GMT

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

Signature

By persisting in your path, though you forfeit the little, you gain the
great.

> Add this as the first line
>
[quoted text clipped - 64 lines]
> > > > > >
> > > > > > Thanks in advance.

Reply to this Message