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:<pre>
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</pre>

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.

_________________<font color = green> 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:<pre>
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</pre>

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.

_________________<font color = green> 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.

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