insert value every nth row in a cell on that row

%3CLINGO-SUB%20id%3D%22lingo-sub-1617417%22%20slang%3D%22en-US%22%3Einsert%20value%20every%20nth%20row%20in%20a%20cell%20on%20that%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1617417%22%20slang%3D%22en-US%22%3E%3CP%3EI%20know%20how%20to%20highlight%20every%20nth%20row%20in%20a%20sheet%2C%20I%20also%20need%20to%20place%20a%20name%20in%20a%20cell%20on%20the%20highlighted%20row.%26nbsp%3B%20For%20instance%2C%20I%20would%20like%20to%20place%20%22Sally%22%20in%20column%20R%20for%20every%20100th%20row.%26nbsp%3B%20Please%20help%20with%20this.%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1617417%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1617725%22%20slang%3D%22en-US%22%3ERe%3A%20insert%20value%20every%20nth%20row%20in%20a%20cell%20on%20that%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1617725%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F773851%22%20target%3D%22_blank%22%3E%40BuddyGlenn%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20use%20the%20mod%20function%20and%20wrap%20it%20in%20If%20function%2C%20below%20example%20might%20be%20helpful%20where%20every%205th%20row%20I'm%20adding%20the%20name%20Sally%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(MOD(%24A2%2C5)%3D0%2C%22Sally%22%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222020-08-28_05-42-27.png%22%20style%3D%22width%3A%20578px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F215188iA6A07A439DA1BE89%2Fimage-dimensions%2F578x423%3Fv%3D1.0%22%20width%3D%22578%22%20height%3D%22423%22%20title%3D%222020-08-28_05-42-27.png%22%20alt%3D%222020-08-28_05-42-27.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%2C%20Faraz%20Shaikh%20%7C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%23808080%22%3EIf%20you%20find%20the%20above%20solution%20resolved%20your%20query%20don't%20forget%20mark%20as%26nbsp%3B%3CSPAN%3EOfficial%2FBest%20Answer%26nbsp%3Bto%20help%20the%20other%20members%20find%20it%20more%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1618334%22%20slang%3D%22en-US%22%3ERe%3A%20insert%20value%20every%20nth%20row%20in%20a%20cell%20on%20that%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1618334%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F156456%22%20target%3D%22_blank%22%3E%40Faraz%20Shaikh%3C%2FA%3E%26nbsp%3BThank%20you%20for%20your%20help%20Faraz!%26nbsp%3B%20I%20am%20trying%20to%20add%20%22Sally%22%20to%20column%20S%20(19)%20for%20every%20200th%20row%20in%20my%20spreadsheet.%26nbsp%3B%20%26nbsp%3BI%20am%20able%20to%20highlight%20every%20row%20using%20conditional%20formatting%20with%20this%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DMOD(ROW()%2C200)%3D0%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20the%20above%20works%20highlighting%20the%20rows%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20trying%20to%20incorporate%20adding%20%22Sally%22%20in%20column%2019%20for%20each%20row%20hightlighted.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(MOD(%24A2%2C19)%3D0%2C%22Sally%22%2C%22%22)%20will%20put%20in%20correct%20column%2C%20I%20need%20it%20to%20be%20every%20200th%20column.%26nbsp%3B%20I%20cannot%20seem%20to%20get%20the%20correct%20syntax%20for%20this.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1620561%22%20slang%3D%22en-US%22%3ERe%3A%20insert%20value%20every%20nth%20row%20in%20a%20cell%20on%20that%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1620561%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F773851%22%20target%3D%22_blank%22%3E%40BuddyGlenn%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ecan%20you%20please%20attached%20the%20sample%20data%20for%20you%20error%20and%20what%20you%20need%20enter%20manually%20in%20the%20file%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%2C%20Faraz%20Shaikh%20%7C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%23808080%22%3EIf%20you%20find%20the%20above%20solution%20resolved%20your%20query%20don't%20forget%20mark%20as%26nbsp%3B%3CSPAN%3EOfficial%2FBest%20Answer%26nbsp%3Bto%20help%20the%20other%20members%20find%20it%20more%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1620897%22%20slang%3D%22en-US%22%3ERe%3A%20insert%20value%20every%20nth%20row%20in%20a%20cell%20on%20that%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1620897%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F773851%22%20target%3D%22_blank%22%3E%40BuddyGlenn%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(NOT(MOD(ROW()%2C19))*NOT(MOD(COLUMN()%2C200))%2C%20%22Sally%22%2C%20%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1621845%22%20slang%3D%22en-US%22%3ERe%3A%20insert%20value%20every%20nth%20row%20in%20a%20cell%20on%20that%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1621845%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThank%20you%20very%20much%20Sergei!%20I%20had%20to%20have%20completed%20by%20Friday%20afternoon%20so%20I%20did%20a%20quick%20macro%20that%20worked.%26nbsp%3B%20I%20will%20get%20your%20solution%20a%20try%20as%20well!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20TagHighlightRows()%3C%2FP%3E%3CP%3EDim%20AD%20As%20Integer%3CBR%20%2F%3EDim%20SB%20As%20Integer%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EAD%20%3D%20100%3CBR%20%2F%3ESB%20%3D%20120%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EDo%20Until%20AD%20%26gt%3B%2016400%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECells(AD%2C%2019).Value%20%3D%20%22AD%22%3CBR%20%2F%3ECells(AD%2C%2019).Interior.Color%20%3D%20vbGreen%3CBR%20%2F%3E%3CBR%20%2F%3ECells(SB%2C%2019).Value%20%3D%20%22SB%22%3CBR%20%2F%3ECells(SB%2C%2019).Interior.Color%20%3D%20vbMagenta%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EAD%20%3D%20AD%20%2B%20700%3CBR%20%2F%3ESB%20%3D%20SB%20%2B%20700%3CBR%20%2F%3E%3CBR%20%2F%3ELoop%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1621846%22%20slang%3D%22en-US%22%3ERe%3A%20insert%20value%20every%20nth%20row%20in%20a%20cell%20on%20that%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1621846%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F156456%22%20target%3D%22_blank%22%3E%40Faraz%20Shaikh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20Faraz!%20I%20had%20to%20have%20completed%20by%20Friday%20afternoon%20so%20I%20did%20a%20quick%20macro%20that%20worked.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20TagHighlightRows()%3C%2FP%3E%3CP%3EDim%20AD%20As%20Integer%3CBR%20%2F%3EDim%20SB%20As%20Integer%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EAD%20%3D%20100%3CBR%20%2F%3ESB%20%3D%20120%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EDo%20Until%20AD%20%26gt%3B%2016400%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECells(AD%2C%2019).Value%20%3D%20%22AD%22%3CBR%20%2F%3ECells(AD%2C%2019).Interior.Color%20%3D%20vbGreen%3CBR%20%2F%3E%3CBR%20%2F%3ECells(SB%2C%2019).Value%20%3D%20%22SB%22%3CBR%20%2F%3ECells(SB%2C%2019).Interior.Color%20%3D%20vbMagenta%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EAD%20%3D%20AD%20%2B%20700%3CBR%20%2F%3ESB%20%3D%20SB%20%2B%20700%3CBR%20%2F%3E%3CBR%20%2F%3ELoop%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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