Advance 1 row every... Now with weird problem ARGH!!!!

yoko

Active Member
Joined
Sep 5, 2006
Messages
349
Hi,

I want a macro that every time its run it will advance 1 row

Something like

Code:
' Out side the macro
Dim row1 As Integer
row1 = 36

' The macro its self
Range ("Arow1:Irow1).select
' Format something
row1 = row1 + 1

But this does not work... any ideas?

Cheers!
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,311
Office Version
  1. 365
Platform
  1. Windows
You may need a loop.

I am not sure what you are trying to accomplish with your code, and it has some errors in it. Can you explain a little more exactly what you are trying to do?
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

would you need something like this ?
Code:
Sub test()
Dim row1 As Long
row1 = 36

    For row1 = 36 To 50
    Range("A" & row1 & ":I" & row1) = row1
    Next row1
End Sub
"= row1" is just an example to do something with the range

kind regards,
Erik
 

yoko

Active Member
Joined
Sep 5, 2006
Messages
349
Ah wonderfull, bit i needed was...

Code:
Range("A" & row1 & ":I" & row1).Select

So now the final code looks like this.

Code:
Sub format()
Dim row1 As Integer
row1 = Range("p1")

    Range("A" & row1 & ":I" & row1).Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$I" & row1 & "=""Y"""
    Selection.FormatConditions(1).Interior.ColorIndex = 4
row1 = row1 + 1
Range("P1") = row1
End Sub

I set P1 to = 36 as I needed this macro to effect all rows after 35.

The aim was that I a database and when ever a "Y" is entered in to column I the whole row goes green so I used conditional formatting to do this.

I'd be interested to see if there are any other ways to achieve the same thing.

Cheers
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

you don't need the "selection"
Code:
    With Range("A" & row1 & ":I" & row1)
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$I" & row1 & "=""Y"""
    .FormatConditions(1).Interior.ColorIndex = 4
    End With

do you want to add conditional format each time an entry is made ?
why not set it in advance ?

if you want to change an entire range at once, you don't need to loop

(in a hurry, have to leave, hope everything is OK in this post)
 

yoko

Active Member
Joined
Sep 5, 2006
Messages
349
Ah this is where my lack of programming kicks in. What’s the difference between using 'selection' and 'with'

I'm pretty new to vba, I tend to record a macro the cut and past the bits I need into a loop or other macro.

Cheers
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

sorry, I skipped a step
instead of selecting you can do the operations "directly"

Code:
Range("A1") = 50
Range("A1").Interior.ColorIndex = 3
Range("A1").etcetera

your code could have read
Code:
    Range("A" & row1 & ":I" & row1).FormatConditions.Delete
    Range("A" & row1 & ":I" & row1).FormatConditions.Add Type:=xlExpression, Formula1:="=$I" & row1 & "=""Y"""
    Range("A" & row1 & ":I" & row1).FormatConditions(1).Interior.ColorIndex = 4


when you are refering to the same "object" all time you can use With
this makes code more clear, readable and will run faster

imagine you are talking about painting your house
In my house on the first floor in the left room there should be painted one wall in red. In my house on the first floor in the left room the wall at the other side must be painted yellow.
You wouldn't say it that way: you wouldn't repeat the first part, since your talking-partner knows it...

isn't this beautiful :)
Code:
With myhouse

    With .firstfloor
        With .leftroom
        .leftwall.paint.red
        .rightwall.paint.yellow
        End With
        With .rightroom
        .leftwall.paint.blue
        End With
    End With
    
    With .secondfloor
        With ...
etcetera

End With
see the DOTs
check the helpfiles !

kind regards,
Erik
 

yoko

Active Member
Joined
Sep 5, 2006
Messages
349
Ok i've done this.

Code:
Sub format()
'
' format Macro
' Macro recorded 13/10/2006 by me
'
Dim switch
switch = True
Dim row1 As Integer
        row1 = Range("P1")
    Do
        Do While row1 < 150
        With Range("A" & row1 & ":I" & row1)
            .FormatConditions.Delete
            .FormatConditions.Add Type:=xlExpression, Formula1:="=$I" & row1 & "=""Y"""
            .FormatConditions(1).Interior.ColorIndex = 4
        End With
        row1 = row1 + 1
        Range("P1") = row1
        If row1 = 150 Then
            switch = False
            Exit Do
        End If
    Loop
    Range("A1").Select
Loop Until siwtch = False
    
End Sub

Which worked a treat on Friday now I've come back and the conditional formatting is messed up, what’s happens normally is when you put a Y in I2 the whole row, A to I, goes green but now its all over the place, the first 5 rows haves Y's in column I but are not formatted the next 5-6 have Y's and are formatted but not to the corresponding I column so for example row 7 is green but goes white if you change the Y in row 11 to anything else. WHY IS THIS!! ARGH

I've not changed the code since last week either

Cheers
EDIT: When I check the conditional format on I2 after running the macro it says =$I65526="Y" yet I've set it to print the value of row1 to cell P1 and when the macro runs you see P1 count from 1 to 150. It doesnt make sence! *Cries*
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
you need a "$" to "fix" the row for conditional format
.Add Type:=xlExpression, Formula1:="=$I$" & row1 & "=""Y"""
also the "switch" is not needed
in fact you don't even have a working switch, since there is a typo in your code
(you could have been noticed of this typo if you had "option explicit" on top of your code)

this is your code
with the "$"
quoted out some lines which are not necessary
indented another way for more clarity (perhaps that's personal)
Code:
Sub format()
'
' format Macro
' Macro recorded 13/10/2006 by me
'
'Dim switch
'switch = True
Dim row1 As Integer
        row1 = Range("P1")
        
    'Do
        Do While row1 < 150
            With Range("A" & row1 & ":I" & row1).FormatConditions
            .Delete
            .Add Type:=xlExpression, Formula1:="=$I$" & row1 & "=""Y"""
            .Item(1).Interior.ColorIndex = 4
            End With
        row1 = row1 + 1
        Range("P1") = row1
            If row1 = 150 Then
                'switch = False
                Exit Do
            End If
        Loop
    Range("A1").Select
    'Loop Until siwtch = False
    
End Sub

next code
added "on error" in case P1 has no number
P1 must be a valid rownumber: added some code
added "Option Explicit"
deleted unneccesary lines
changed datatype of row1 to "Long"
you would not need all these error checks if you are really sure P1 can never be wrong datatype nor wrong rownumber
Code:
Option Explicit

Sub format()
'
' format Macro
' Macro recorded 13/10/2006 by me
'
Dim row1 As Long

On Error Resume Next
row1 = Range("P1")
On Error GoTo 0

row1 = Range("P1")
    If row1 < 1 Or row1 > Rows.Count Then
    MsgBox "error message", 48, "ERROR title"
    Exit Sub
    End If

    Do While row1 < 150

        With Range("A" & row1 & ":I" & row1).FormatConditions
        .Delete
        .Add Type:=xlExpression, Formula1:="=$I$" & row1 & "=""Y"""
        .Item(1).Interior.ColorIndex = 4
        End With

    row1 = row1 + 1
    If row1 = 150 Then Exit Do
    Loop

Range("P1") = row1
Range("A1").Select

    
End Sub
this was for learning purposes since you don't need to loop, unless I'mm missing something
if you want to change an entire range at once, you don't need to loop
if this is working for you I can help you with the non-loop-code

best regards,
Erik
 

yoko

Active Member
Joined
Sep 5, 2006
Messages
349
You sir are a legend.

The whole switch things I got from the excel help example, I just copied it in and then edited the code.

The loop was so that it would set the conditional format for a row of cells then move on to the next row until a give number or rows had been done.

Cheers!
 

Forum statistics

Threads
1,136,616
Messages
5,676,835
Members
419,653
Latest member
analyticalchemist94

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
Top