Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: How to make it easier?

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    1,451
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have the following macro :

    If Cells(x, 4) > Cells(x, 5) And Cells(x, 5) > Cells(x, 6) And Cells(x, 6) > Cells(x, 7) And Cells(x, 7) > Cells(x, And Cells(x, > Cells(x, 9) And Cells(x, 9) > Cells(x, 10) And Cells(x, 10) > Cells(x, 11) Then
    Range(Cells(x, 4), Cells(x, 256).End(xlToLeft)).Interior.ColorIndex = 33

    Because sometimes there are more cells to compare,is there an easier way to write this macro?

    Thanks

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Something like this should be easier:


    Public Sub main()

    Dim oLastColumn As Range
    Dim oUsedRange As Range
    Dim oRange As Range
    Dim bFormat As Boolean
    Dim i As Integer

    Set oLastColumn = Range("IV" & 1).End(xlToLeft)
    Set oUsedRange = Range(Range("A" & 1), oLastColumn)

    bFormat = False

    For Each oRange In oUsedRange
    If oRange.Value <= oRange.Offset(0, 1).Value Then
    MsgBox "Cells will not be formatted."
    Exit For
    Else
    bFormat = True
    End If
    Next

    If bFormat = True Then
    Range(Cells(1, 1), Cells(1, 256).End(xlToLeft)).Interior.ColorIndex = 33
    End If

    End Sub


    I modified your code slightly. I am only doing this for 1 row of data so I removed your "x" variable and just made "A1" the first cell in the range.

    Basically, this will find the last used cell in the row and set a Range based on this and the first cell. (A1 in my case)

    It then loops through this range to make sure that the series is decreasing in size all the way along the used range.

    I hope this helps.

    PS. I assumed that there were no blank cells in the range.

    _________________
    [b] Mark O'Brien

    [ This Message was edited by: Mark O'Brien on 2002-04-15 09:18 ]

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    1,451
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-15 09:17, Mark O'Brien wrote:
    Something like this should be easier:


    Public Sub main()

    Dim oLastColumn As Range
    Dim oUsedRange As Range
    Dim oRange As Range
    Dim bFormat As Boolean
    Dim i As Integer

    Set oLastColumn = Range("IV" & 1).End(xlToLeft)
    Set oUsedRange = Range(Range("A" & 1), oLastColumn)

    bFormat = False

    For Each oRange In oUsedRange
    If oRange.Value <= oRange.Offset(0, 1).Value Then
    MsgBox "Cells will not be formatted."
    Exit For
    Else
    bFormat = True
    End If
    Next

    If bFormat = True Then
    Range(Cells(1, 1), Cells(1, 256).End(xlToLeft)).Interior.ColorIndex = 33
    End If

    End Sub


    I modified your code slightly. I am only doing this for 1 row of data so I removed your "x" variable and just made "A1" the first cell in the range.

    Basically, this will find the last used cell in the row and set a Range based on this and the first cell. (A1 in my case)

    It then loops through this range to make sure that the series is decreasing in size all the way along the used range.

    I hope this helps.

    PS. I assumed that there were no blank cells in the range.

    Hi,

    the x in my macro means the rownumber,because there are +/- 5000 rows
    After each row,there is the following code in my macro : X = X + 1
    The rows who must descreasing are: x,4 to the last filled column.
    Is your macro maded in that sense?
    Many thanks.

    _________________
    [b] Mark O'Brien

    [ This Message was edited by: Mark O'Brien on 2002-04-15 09:18 ]

  4. #4
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It's not set up for (x,4), but that's easily changed. I understand that "x" is your integer to loop through the rows, but I didn't know how you were determining this, that's why I coded for 1 row only. Ive modified my code to start at (x,4) but you're going to have to put it within your row loop for it to work. I've marked in comments where you'll need to start and end your loop.


    Dim oUsedRange As Range
    Dim oRange As Range
    Dim bFormat As Boolean
    Dim i As Integer

    'Begin loop for row using x
    Set oLastColumn = Range("IV" & x).End(xlToLeft)
    Set oUsedRange = Range(Cells(x, 4), oLastColumn)

    bFormat = False

    For Each oRange In oUsedRange
    If oRange.Value <= oRange.Offset(0, 1).Value Then
    MsgBox "Cells will not be formatted."
    Exit For
    Else
    bFormat = True
    End If
    Next

    If bFormat = True Then
    oUsedRange.Interior.ColorIndex = 33
    End If
    'End row loop.


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •