Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change font to Italic
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. -- By persisting in your path, though you forfeit the little, you gain the great. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change font to Italic
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(xlCellTy peConstants, xlTextValues).Select 'ActiveSheet.UsedRange.SpecialCells(xlCellTypeCons tants, 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" wrote: 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. -- By persisting in your path, though you forfeit the little, you gain the great. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change font to Italic
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 -- By persisting in your path, though you forfeit the little, you gain the great. "Mike H" wrote: 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(xlCellTy peConstants, xlTextValues).Select 'ActiveSheet.UsedRange.SpecialCells(xlCellTypeCons tants, 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" wrote: 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. -- By persisting in your path, though you forfeit the little, you gain the great. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change font to Italic
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" wrote: 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 -- By persisting in your path, though you forfeit the little, you gain the great. "Mike H" wrote: 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(xlCellTy peConstants, xlTextValues).Select 'ActiveSheet.UsedRange.SpecialCells(xlCellTypeCons tants, 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" wrote: 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. -- By persisting in your path, though you forfeit the little, you gain the great. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change font to Italic
Mike,
I keep getting an error variable not defined in this line: If c.Font.ColorIndex = 3 Then -- By persisting in your path, though you forfeit the little, you gain the great. "Mike H" wrote: 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" wrote: 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 -- By persisting in your path, though you forfeit the little, you gain the great. "Mike H" wrote: 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(xlCellTy peConstants, xlTextValues).Select 'ActiveSheet.UsedRange.SpecialCells(xlCellTypeCons tants, 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" wrote: 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. -- By persisting in your path, though you forfeit the little, you gain the great. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change font to Italic
Add this as the first line
Dim c As Range Mike "DavidH56" wrote: Mike, I keep getting an error variable not defined in this line: If c.Font.ColorIndex = 3 Then -- By persisting in your path, though you forfeit the little, you gain the great. "Mike H" wrote: 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" wrote: 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 -- By persisting in your path, though you forfeit the little, you gain the great. "Mike H" wrote: 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(xlCellTy peConstants, xlTextValues).Select 'ActiveSheet.UsedRange.SpecialCells(xlCellTypeCons tants, 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" wrote: 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. -- By persisting in your path, though you forfeit the little, you gain the great. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change font to Italic
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(xlCell TypeConstants, _ 'xlTextValues).Select 'ActiveSheet.UsedRange.SpecialCells(xlCellTypeCons tants, 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 -- By persisting in your path, though you forfeit the little, you gain the great. "Mike H" wrote: Add this as the first line Dim c As Range Mike "DavidH56" wrote: Mike, I keep getting an error variable not defined in this line: If c.Font.ColorIndex = 3 Then -- By persisting in your path, though you forfeit the little, you gain the great. "Mike H" wrote: 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" wrote: 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 -- By persisting in your path, though you forfeit the little, you gain the great. "Mike H" wrote: 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(xlCellTy peConstants, xlTextValues).Select 'ActiveSheet.UsedRange.SpecialCells(xlCellTypeCons tants, 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" wrote: 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. -- By persisting in your path, though you forfeit the little, you gain the great. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Font/Color/Bold/Italic Remain Same | Excel Discussion (Misc queries) | |||
Macro to Simulate CF Italic Red Font based on Formula Criteria | Excel Programming | |||
Macro to Simulate CF Italic Red Font based on Formula Criteria | Excel Programming | |||
Lookup and change to italic italic | Excel Worksheet Functions | |||
change format of cells (bold, italic) based on one cell's answer | Excel Discussion (Misc queries) |