Being a newbie has it's frustrating moments . I've been at my system all day trying to work my way around what should normally be a simple thing to solve if i knew what i was doing, sadly, I don't!
I've been working on a document and wondered if anyone could help with a loop statement to run through multiple rows and columns.I have attached an excel document for this.
Here's what i'd like my code to do;
>> compare values in the unshaded rows with values in the shaded rows for each column. That is, compare values in D2 with D3. . . J2 with J3. . .P2 with P3. . . V2 with V3. . . .etc till the last value in the unshaded row, in this case, till AB2
>>>> I'd like this to be repeated for each unshaded row, and skipping the shaded rows. Therefore, in this case, the next iteration will compare D4 with D5. . .J4 with J5. . . and will continue this till AH4 is compared with AH5
>>>>> For each comparison, what i intend to do is;
** if D2 < D3, and J2 < J3, and P2 < P3 and V2 < V3, and AB2<AB3, highlight the unshaded row with a certain color.
**If not, then if D2<D3 and J2<J3 and P2 < P3 and V2 < V3, highlight the unshaded row with a certain color.
** if not, then if D2<D3 and J2<J3 and P2<P3, highlight the unshaded row with a certain color
** if not, then if D2<D3 and J2<J3, highlight the unshaded row with a certain color.
** if not, then if D2<D3, highlight the unshaded row with a certain color
>>> if not, then go to the next unshaded row, i.e D4
I would be eternally grateful if you could include a code that is easy for a beginner to grasp, and even more grateful if you include points on how the code does what it does.
In re: "P2 with P3. . . V2 with V3. . . ..etc till the last value in the unshaded row"
It appears that you are looking at every sixth column. Is that the case?
On the other hand, your stated conditions only go to column AB. How would column AH meeting the criteria change the result you want?
You say shaded vs. unshaded row. Is it the shading that is important or whether the row number is odd or even?
Is there any case where row 2 might get shaded (by a clumsy fingered user), should the 2 override the (un)shaded-ness.
Should the routine halt once it has found a row to color?
Thank you for your response . Yes, I am looking at every sixth column
----The stated conditions apply to every sixth column in the spreadsheet as long as there is data in the unshaded cell.
----no, the shading doesn't count as long as long as the even rows are being compared with corresponding odd ones, so in case some Mr clumsy fingered user comes along, it wouldn't have changed the desired outcome
--- Yes, i'd be happy to throw a party if the routine halts once it has found a row to color.
I appreciate your help so far on this Mikerickson.
I think this will do what you want.
[VBA]Sub test()
Dim rowNum As Long, colNum As Long
Dim setColor As Long
With ThisWorkbook.Sheets("Sheet1")
For rowNum = 2 To .Cells(.Rows.Count, 4).End(xlUp).Row Step 2
colNum = 4
setColor = 0
Do
If Val(CStr(.Cells(rowNum + 1, colNum))) < Val(CStr(.Cells(rowNum, colNum))) Then Exit Do
colNum = colNum + 6
Loop Until Not IsNumeric(.Cells(rowNum, colNum))
Select Case colNum
Case 4
Rem column D failed, do next row
Case 10
Rem column D passed, J failed
setColor = 3
Case 16
Rem D,J passed
setColor = 5
Case 22
setColor = 6
Case 28
setColor = 7
Case Else
setColor = 33
End Select
If 0 < setColor Then
.Cells(rowNum + 1, 1).EntireRow.Interior.ColorIndex = setColor
Exit For: Rem optional stop coloring once condition is met
End If
Next rowNum
End With
End Sub[/VBA]The For rowNum loop is pretty straight forward since the Step argument is perfect for this problem
The interior Do loop tests for which column fails the unshaded<shaded requirement and then exits that sub loop.
The Select Case sets the appropriate color depending on which column was the first to fail the condition.
The Exit For stops the rowNum loop.
If you are using Rand, you might want to start the routine with something that shades every odd row grey.
Thanks again for this, it's helped me improve my understanding of VBA and I've moved a few notch from being a stark rookie.
Can i ask, is there a way i can tweak this around such that the routine doesn't halt once it colors a row? i.e the routine continues till the last row in the worksheet?
Loop through multiple columns and rows at once.
. I've been at my system all day trying to work my way around what should normally be a simple thing to solve if i knew what i was doing, sadly, I don't!
Reply With Quote
. Yes, I am looking at every sixth column