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

Vanish29

New Member
Joined
Apr 28, 2016
Messages
26
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:
Rich (BB 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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
What exactly doesn't work in the snippet you posted and where is the value of the variable Utill set?
 
Upvote 0
Hey Joemo,
I apologize for the confusion.
Here is the code without the Cells.Find
Code:
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.
 
Upvote 0
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.
 
Upvote 0
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.
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
Code:
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
Code:
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
 
Upvote 0
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.
Rich (BB code):
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
 
Upvote 0
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
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top