Forum Discussion

Copper Contributor

Aug 27, 2020

insert value every nth row in a cell on that row

I know how to highlight every nth row in a sheet, I also need to place a name in a cell on the highlighted row. For instance, I would like to place "Sally" in column R for every 100th row. Please h...

excel

BuddyGlenn

Copper Contributor

Aug 28, 2020

ExcelExciting Thank you for your help Faraz! I am trying to add "Sally" to column S (19) for every 200th row in my spreadsheet. I am able to highlight every row using conditional formatting with this formula:

=MOD(ROW(),200)=0

and the above works highlighting the rows

I was trying to incorporate adding "Sally" in column 19 for each row hightlighted.

I know

=IF(MOD($A2,19)=0,"Sally","") will put in correct column, I need it to be every 200th column. I cannot seem to get the correct syntax for this.

SergeiBaklan

Diamond Contributor

Aug 29, 2020

BuddyGlenn

Perhaps

=IF(NOT(MOD(ROW(),19))*NOT(MOD(COLUMN(),200)), "Sally", "")

BuddyGlenn
Copper Contributor
Aug 31, 2020
SergeiBaklan Thank you very much Sergei! I had to have completed by Friday afternoon so I did a quick macro that worked. I will get your solution a try as well!

Sub TagHighlightRows()
Dim AD As Integer
Dim SB As Integer

AD = 100
SB = 120

Do Until AD > 16400

Cells(AD, 19).Value = "AD"
Cells(AD, 19).Interior.Color = vbGreen

Cells(SB, 19).Value = "SB"
Cells(SB, 19).Interior.Color = vbMagenta

AD = AD + 700
SB = SB + 700

Loop

End Sub