How to make it easier?

[verluc]

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

[Mark O'Brien]

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 ]

[verluc]

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 ]

[Mark O'Brien]

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.