| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add text in a cell based on colour of text in another cell
Hi
I have recorded a macro to add "For Sale" into cell J3 if the text in column A3 is red. I added an IF THEN and it works but how do I get it to repeat in all cells as in cell J4 needs to be set to "For Sale" if the text in cell A4 is red all the way down to J1311. Sub Macro4() Range("A3").Select If Selection.Font.ColorIndex = 3 Then Range("J3").Select ActiveCell.FormulaR1C1 = "For Sale" End If End Sub Thanks in advance Ian |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Add text in a cell based on colour of text in another cell
Dim CRng As Range
Set CRng = Range("J3:J1311") For Each cl In CRng.Cells If cl.Offset(0, -9).Font.ColorIndex = 3 Then cl.Value = "For Sale" End If Next cl Hope this Helps Duncan Ian Richardson ACITP wrote: Hi I have recorded a macro to add "For Sale" into cell J3 if the text in column A3 is red. I added an IF THEN and it works but how do I get it to repeat in all cells as in cell J4 needs to be set to "For Sale" if the text in cell A4 is red all the way down to J1311. Sub Macro4() Range("A3").Select If Selection.Font.ColorIndex = 3 Then Range("J3").Select ActiveCell.FormulaR1C1 = "For Sale" End If End Sub Thanks in advance Ian |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Add text in a cell based on colour of text in another cell
Actually, if running this more than once and the cells in A3 are likely
to change, then reset the words if text not red? (just add an 'else') Dim CRng As Range Set CRng = Range("J3:J1311") For Each cl In CRng.Cells If cl.Offset(0, -9).Font.ColorIndex = 3 Then cl.Value = "For Sale" Else cl.Value = "" End If Next cl HTH Duncan Duncan wrote: Dim CRng As Range Set CRng = Range("J3:J1311") For Each cl In CRng.Cells If cl.Offset(0, -9).Font.ColorIndex = 3 Then cl.Value = "For Sale" End If Next cl Hope this Helps Duncan Ian Richardson ACITP wrote: Hi I have recorded a macro to add "For Sale" into cell J3 if the text in column A3 is red. I added an IF THEN and it works but how do I get it to repeat in all cells as in cell J4 needs to be set to "For Sale" if the text in cell A4 is red all the way down to J1311. Sub Macro4() Range("A3").Select If Selection.Font.ColorIndex = 3 Then Range("J3").Select ActiveCell.FormulaR1C1 = "For Sale" End If End Sub Thanks in advance Ian |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Add text in a cell based on colour of text in another cell
Hi Duncan
Thanks for the quick reply. Works like a dream!! Ian "Duncan" wrote: Actually, if running this more than once and the cells in A3 are likely to change, then reset the words if text not red? (just add an 'else') Dim CRng As Range Set CRng = Range("J3:J1311") For Each cl In CRng.Cells If cl.Offset(0, -9).Font.ColorIndex = 3 Then cl.Value = "For Sale" Else cl.Value = "" End If Next cl HTH Duncan Duncan wrote: Dim CRng As Range Set CRng = Range("J3:J1311") For Each cl In CRng.Cells If cl.Offset(0, -9).Font.ColorIndex = 3 Then cl.Value = "For Sale" End If Next cl Hope this Helps Duncan Ian Richardson ACITP wrote: Hi I have recorded a macro to add "For Sale" into cell J3 if the text in column A3 is red. I added an IF THEN and it works but how do I get it to repeat in all cells as in cell J4 needs to be set to "For Sale" if the text in cell A4 is red all the way down to J1311. Sub Macro4() Range("A3").Select If Selection.Font.ColorIndex = 3 Then Range("J3").Select ActiveCell.FormulaR1C1 = "For Sale" End If End Sub Thanks in advance Ian |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do i change cell text colour based on value in other cell | Excel Worksheet Functions | |||
| how to colour a cell green if another cell is filled with text | Excel Discussion (Misc queries) | |||
| Cell Colour based on text in Range of cells | Excel Programming | |||
| Cell Colour based on text in Range of cells | Excel Programming | |||
| Deleting Rows based on text in cell & formatting cell based on text in column beside it | Excel Programming | |||
Linear Mode
