insert value every nth row in a cell on that row

Copper Contributor

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 help with this.

Thanks

6 Replies

Hi @BuddyGlenn,

 

You can use the mod function and wrap it in If function, below example might be helpful where every 5th row I'm adding the name Sally

 

 

=IF(MOD($A2,5)=0,"Sally","")

 

 

2020-08-28_05-42-27.png

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer to help the other members find it more

 

@Faraz Shaikh 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.

Hi @BuddyGlenn 

 

can you please attached the sample data for you error and what you need enter manually in the file 

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer to help the other members find it more

@BuddyGlenn 

Perhaps

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

@Sergei Baklan 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

@Faraz Shaikh 

Thank you very much Faraz! I had to have completed by Friday afternoon so I did a quick macro that worked.  

 

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