loop through cells Col_A, then Col_B etc

[rcr1991]

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.

 

[Tazguy37]

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?

 

[rcr1991]

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?

 

[XLGibbs]

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.

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...

 

[Tazguy37]

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!

 

[rcr1991]

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

 

[rcr1991]

figured out that it needed an endif.

 

[Norie]

rcr1991

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