MOD Function Issue

[tcfd1166]

Is there anyway to start the MOD function on a specific row? My current excel file I have the following code:

If ccells.Row Mod 5 <> 0 Then
Select Case ccells

I have other code set to color columns based off of text entered in all rows except the ones the MOD function applies to. The MOD function applies to every 5th row, which currently works, but I am making a change in my rows that will change the function from 5 to 4, but it still starts on the 5th row as before. Can you tell VBA on what row to start the function, or is there another way?

 

[tcfd1166]

Or is there anyway to have multiple MOD functions? My first row it would need to apply to is row 9, then every 4 after that.

 

[Jeffrey Mahoney]

I usually subtract the row of the header. This is an example of using an Excel Table header

HdrRow = Range("GenTbl[[#Headers],[PID]]").row
If (ccells.Row - HdrRow) Mod 5 = 0 Then

 

[Jeffrey Mahoney]

Or is there anyway to have multiple MOD functions? My first row it would need to apply to is row 9, then every 4 after that.

Same example as posted above with 4 instead of 5

 

[tcfd1166]

The code is giving an error.

GenTbl = ?
#Headers = 8
PID = ?

 

[Jeffrey Mahoney]

This is for my table header. If you know the row that your header is on, you can just make HdrRow = 2 or whatever. You'll need to declare HdrRow like this:
Dim HdrRow as long

HdrRow = 2

----------------
"HdrRow = Range("GenTbl[[#Headers],[PID]]").row"

 

[jasonb75]

My first row it would need to apply to is row 9, then every 4 after that.

There are a few ways, Jeffrey's suggestion only works if you have structured tables in your sheet.

One way of working with a normal range would be

If ccells.Row Mod 4 = 1 And ccells.Row >= 9 Then

There may be other ways but we would need to see the rest of your code to determine which would be the best one to use.

 

[tcfd1166]

Current Code is:

Dim ccells As Range

If ccells.Row Mod 5 <> 0 Then
Select Case ccells

Case "Test"
ccells.Interior.ColorIndex = 20

Case Else
ccells.Interior.Colorindex = -4142
End Select
End If

Basically, I have columns that color based on a cell entry "Test" and it colors that cell in the particular column based on the entry "Test". If it doesn't see "Test", it clears the color. I am currently having every 5th row as a heavy border black color to distinguish groups of columns. I am in the process of moving from every 5th row to every 4th row as a border black color. In my current file, the MOD 5 <>0 works, since the 5th and 10th row are borders. My new code will start the borders at the 9th row and then every 4th row after. That's my issue.

 

[jasonb75]

With what you're doing the line that I suggested should work correctly.

I note that you have declared ccells as a variable but have not set a range to it so there will still be more to the code. Also, I would assume that you are looping through ccells somehow in which case you could possibly use a step in the loop, negating the need for the Mod line (this would not work if the code is doing something different to the other rows that do not meet the mod criteria at the same time).

 

[tcfd1166]

Here is my code that checks the range.

'Color Cells by Customer
Dim ccells As Range

'Check Each Cell In Range
For Each ccells In Sheets(1).Range("H6:H73,M6:M73,R6:R73,W6:W73")
mytext = ccells.Value

If ccells.Row Mod 4 <> 0 And ccells.Row >= 9 Then
'Set Interior Color Based On Contents
'ccells.Select

Select Case ccells
Case "Test"
ccells.Interior.ColorIndex = 20

Case Else
ccells.Interior.Colorindex = -4142
End Select
End If

Right now, that code will make row 9 blank and every 4th row will be black below.
Rows currently are 9(blank), 12,16,20,24.... 12,16,20,24 are black.
I need row 9 to be black and every 4th row after that to be black.
Rows will be 9,13,17,21, etc...