code to dynamically identify every nth value

[kam2004]

Hello All

I have a list of 100000 numbers ordered in an ascending column
I am trying to write a formula to highlight (or just place 'yes' mark in an adjacent cell) every cell where the numerical value is x (the starting number in the ordered sequence) plus n (a value that could be changed).
So successive highlighted cells/rows are n higher than the last highlighted cell.

Please see the attached VBA code which I can make work until I try and rest the base value ("base1") from which I am trying to calculate the next n+12

Sub FindTP(InputValue, rng As Range)


Dim Cell As Range
Dim base1 As Integer
Dim TestValue As Integer

RunTP = "No"
base1 = InputValue

For Each Cell In rng

If Cell.Value > base1 + 12 Then

RunTP = "Yes"
base1 = Cell.Value

End If

Next Cell

End Function
End Sub


Any help to achieve this would be appreciated - the real number list to be analysed is 100000 long and so a laborious manual task otherwise.

Thanks in advance

KB

 

[jasonb75]

Thinking out of the box a little, Assuming numbers in column A, starting in A2 (A1 needs to be empty or contain a text header for this to work).

In B2 and fill down, note that $C$2 represents the desired n value

=IF(A2=MAX(B$1:B1)+$C$2,A2,"No")

Then apply a custom format to column B, "Yes";"Yes";"Yes";@

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Kam()
   Dim Cl As Range
   Dim BaseNum As Long
   
   BaseNum = Range("A1").Value
   For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
      If Cl.Value >= BaseNum + 12 Then
         Cl.Interior.Color = 45678
         BaseNum = BaseNum + 12
      End If
   Next Cell
End Sub

 

[kam2004]

Thanks for your help and speedy reply---i'll give it a try and report back shortly
Kam

 

[kam2004]

Fluff
works great, and I am very grateful, thanks

 

[kam2004]

Jason, also works, again many thanks

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Fluff]

Cross posted Conditional format to highlight cell or row of every x+nth value in a ordered sequence

For future reference
While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.