Do/Loop, If/Then or For/Next - Not sure which to use for this reiteration

[Vanish29]

Hello
Task:
Calculate a value, only if the background of the cell is a certain color, then go to next row.

I have been reading up on the Do/Loop, If/Then and For/Next statements, but having difficulty making any of them work.

Here is the code:

Cells.Find(What:=Util1, after:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, searchdirection:=xlNext, MatchCase:=False _
        , SearchFormat:=True).Offset(1, 1).Activate
If ActiveCell.Interior.Color = 16711935 Then
UpdateRow = ActiveCell.Row
TrueStat = Application.WorksheetFunction.Index(StatCol,UpdateRow)
HoldStat = Application.WorksheetFunction.Sum(TrueStat/RStat)
UpdateStat = Application.WorksheetFunction.Round (HoldStat,4)
ActiveCell.Value =  Round(UpdateStat*Redist1,2)
Else

Thank you for your assistance

 

[JoeMo]

What exactly doesn't work in the snippet you posted and where is the value of the variable Utill set?

 

[Vanish29]

Hey Joemo,
I apologize for the confusion.
Here is the code without the Cells.Find

If ActiveCell.Interior.Color = 16711935 Then
UpdateRow = ActiveCell.Row
TrueStat = Application.WorksheetFunction.Index(StatCol, UpdateRow)
HoldStat = Application.WorksheetFunction.Sum(TrueStat / RStat)
UpdateStat = Application.WorksheetFunction.Round(HoldStat, 4)
ActiveCell.Value = Round(UpdateStat * Redist1, 2)
Else
End If

The issue with this code is it only does the current cell.
I need it to go to the next row and do same If/Then.

 

[JoeMo]

If you want to include a range of cells you will need to define that range first then loop through each cell in the range. This is much better than using ActiveCell which might not be the cell you intended.

If you provide some details about your worksheet layout and what range of cells you want to check for the color fill of interest, someone here can help you.

 

[Vanish29]

Hey Joemo,
The range will be the whole column of the active cell down to a lastrow (lastrow has already been defined)
The Cells.Find gets me to where I need to go, so the column will not be static.

As for adding the loop, that is what I am hoping to have answered.
Should this be a Do/Loop, If/Then or For/Next?
I am not good at creating these, even after looking through guides.

What details would you like from the worksheet?
The ActiveCell that this loop will start in is dynamic and will always be different.
ActiveCell is found with this code.

Cells.Find(What:=Util1, after:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, searchdirection:=xlNext, MatchCase:=False _
        , SearchFormat:=True).Offset(1, 1).Activate

Util1 is a user defined value and finds the correct value and cell each time. The offset portion links to the second cell in the first column to the right.

Here are a couple of my previous attempts, if that helps

For k = 1 To lastRow
If ActiveCell.Interior.Color = 16711935 Then
UpdateRow = ActiveCell.Row
TrueStat = Application.WorksheetFunction.Index(StatCol, UpdateRow)
HoldStat = Application.WorksheetFunction.Sum(TrueStat / RStat)
UpdateStat = Application.WorksheetFunction.Round(HoldStat, 4)
ActiveCell.Value = Round(UpdateStat * Redist1, 2)
End If
Next k

Set RedistCol  = Range(ActiveCell.EntireColumn, ActiveCell.EntireColumn)

For Each cell in RedistCol
If ActiveCell.Interior.Color = 16711935 Then
UpdateRow = ActiveCell.Row
TrueStat = Application.WorksheetFunction.Index(StatCol, UpdateRow)
HoldStat = Application.WorksheetFunction.Sum(TrueStat / RStat)
UpdateStat = Application.WorksheetFunction.Round(HoldStat, 4)
ActiveCell.Value = Round(UpdateStat * Redist1, 2)
Else
End IF
Next cell

 

[JoeMo]

This should get you started, but I can't test the portion of the code in red b/c you haven't provided sufficient detail on your layout or the variables that appear in that portion.

Sub test()
Dim LastRow As Long, cell As Range
LastRow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
Set RedistCol = Range(ActiveCell, Cells(LastRow, ActiveCell.Column))
For Each cell In RedistCol
    If cell.Interior.Color = 16711935 Then
        UpdateRow = cell.Row
        TrueStat = Application.WorksheetFunction.Index(StatCol, UpdateRow)
        HoldStat = Application.WorksheetFunction.Sum(TrueStat / RStat)
        UpdateStat = Application.WorksheetFunction.Round(HoldStat, 4)
        cell.Value = Round(UpdateStat * Redist1, 2)
    End If
Next cell
End Sub

 

[Vanish29]

Hey Joemo,
Wonderful.
The variables in red could easily be substituted for garbage data.
As for the other portion, it worked. I only needed to make a slight tweak.
Here is the Final Code

Cells.Find(What:=Util1, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, searchdirection:=xlNext, MatchCase:=False _
        , SearchFormat:=True).Offset(1, 2).Activate
Set RedistCol = Range(ActiveCell, Cells(lastRow, ActiveCell.Column))
For Each cell In RedistCol
    If cell.Interior.Color = 16711935 Then
        UpdateRow = ActiveCell.Row
        TrueStat = Application.WorksheetFunction.Index(StatCol, UpdateRow)
        HoldStat = Application.WorksheetFunction.Sum(TrueStat / RStat)
        UpdateStat = Application.WorksheetFunction.Round(HoldStat, 4)
        cell.Value = Round(UpdateStat * Redist1, 2)
    End If
    ActiveCell.Offset(1, 0).Activate
Next cell

This is what I needed.
Thank you very much