PDA

View Full Version : Macro replacing text in each row by column



fran2406
05-30-2008, 08:05 AM
Hy everyone,

I want to make a macro that changes automatically the color of the selected cells and also that, in each row, changes the text in column N to “A” (no matter what was the text in that column before).

For changing the color of the selected cells from orange to green I did the following macro (it’s working):

Sub ColorChangetoGreen()
For Each c In Selection
c.Interior.ColorIndex = 4
Next c
End Sub



But I’m not able to make a macro that would overwrite, in the selected rows (i.e. from A2 to I7), the cells that are in column F (Status) with the text “A” (instead of “P”). It doesn’t matter what text was previously in column F. What is important is that all the cells in column F and that are in the selected rows will become with my text (costant).

I tried the following macro

Sub ForEachRow1()
For Each rw In Selection
rw.Offset(0, 5).Range("A1").FormulaR1C1 = "A"
Next rw
End Sub


, but the result is that all the cells on the right of column F are overwritten with "A"s. How can I instruct the macro to only overwrite one cell for every row?


The excel sheet before the running of the macro is in the attached word document.

Any help would be great.

Bob Phillips
05-30-2008, 09:35 AM
Sub ColorChangetoGreen()
Dim rRow As Range
Dim rCell As Range

For Each rRow In Selection.Rows

For Each rCell In rRow

rCell.Interior.ColorIndex = 4
Next rCell

Cells(rRow.Row, "N").Value = "A"
Next rRow
End Sub

fran2406
06-01-2008, 11:48 PM
Thank you El Xid !!!

you solved my problem so perfectly.
You're a genius!
All the best
Fran

mdmackillop
06-02-2008, 12:09 AM
Hi Fran,
If this is solved, please mark it so using the Thread Tools dropdown
Regards
MD