loop through cells Col_A, then Col_B etc

rcr1991

New Member
what is the best way to select column A, run a conditional fxn on each cell in column A. then move on to column B and so the same thing. variable number of rows, but 14 columns always. it was a struggle for me to figure out the function, and now i am really stuck with figuring out the best way to do the loop.

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
rcr1991 said:
run a conditional fxn on each cell in column A
...
it was a struggle for me to figure out the function, and now i am really stuck with figuring out the best way to do the loop.

Got any code to post for what you have working now?

Taz,

I didn't make it clear how baffled I am.

my fxn compares r1c1 to r1c25. if r1c1 > r1c25, then colors r1c1 red. that i figured out how do to.

what i don't know how to get started on is how to select first column A, do my fxn above on each cell to end, then go on to column b and repeat.

I know this is very basic, but that's my level of comprehension right now.

suggestions?

The below will start at cell R1C1 and go through all cells in column A, then go to column B and do the same thing. It first checks the row to determine the last row with data in that column. The comparison always looks at column Y (column 25), so you can tweak the code as needed, or ask for more help. You are not really clear on what you have done, or what you may need though.

Code:
``````Sub CellCompare()

Application.ScreenUpdating = False
For x = 1 To 3
LRow = Cells(65536, x).End(xlUp).row
For y = 1 To LRow
Cells(y, x).Activate
If ActiveCell.Value > Cells(y, 25).Value Then
ActiveCell.Interior.ColorIndex = 3
Next y
Next x

Application.ScreenUpdating = True

End Sub``````

Obviously, if Conditional Formatting has not been tried yet...good point Tazguy...

What you could do instead is use Conditional Formatting. Go to Cell A1, and select Format, Conditional Formatting.

In the left side dropdown, select Formula Is, and on the right, use this formula:
=A1>A25

Choose your format, and click OK.

Then you can use the Format Painter (Formatting Toolbar) to apply this format to any other cells you want.

Hope that helps!

OK, i begin to see light. Conidtional formatting does the cell highlighting, but doesn't answer what this code does, which is looping thorugh cells in a column, then on to next column. this is Eaxcetly what I need to do.

but when i run that code i get error: Next without For. But I see two pairs of for-next statements. why is excel burping on this?

I copied it as written:

Sub CellCompare()

Application.ScreenUpdating = False
For x = 1 To 3
lRow = Cells(65536, x).End(xlUp).Row
For y = 1 To lRow
Cells(y, x).Activate
If ActiveCell.Value > Cells(y, 25).Value Then
ActiveCell.Interior.ColorIndex = 3
Next y
Next x

Application.ScreenUpdating = True

End Sub

figured out that it needed an endif.

rcr1991

As far as I can see there is no need for code for this.

Replies
3
Views
305
Replies
3
Views
146
Replies
3
Views
395
Replies
3
Views
459
Legacy 143009
L
Replies
0
Views
154

1,216,109
Messages
6,128,876
Members
449,476
Latest member
pranjal9

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?

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

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