MrExcel.com - Your One Stop for Excel Tips & Solutions

Macro to change colour of a row and move to next cell in Column

Posted by Tillymint on December 20, 2000 10:08 AM

Can someone make my Christmas and help me with a little problem.

I have an excel sheet and have given one of the columns a name range. I need a macro that will check the first cell in the range and if it meets certain criteria changes that row in the spreadsheet to a colour and move to the next cell and repeat till end.

I have managed to get it to change one row but don't know how to tell it to move to the cell below and repeat the macro.


The code I have used is

Private Sub CommandButton1_Click()
If ATD < 1 Then
Rows("12:12").Select
With Selection.Interior
.ColorIndex = 8
.Pattern = xlSolid
End With
End If
End Sub


Help with this would be much appreciated - Thanks

Posted by thomas venn on December 20, 2000 2:28 PM

Hi,

Try this... Far from elegant, but hopefully, it will work for you.

Cheers,

Thomas

Sub Macro8()
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="1"
Selection.FormatConditions(1).Interior.ColorIndex = 4
ActiveCell.Offset(0, 1).Range("A1").Select
Calculate
'End Sub
'Sub Macro9()

Application.Run "Macro8"
Application.Run "Macro8"
Application.Run "Macro8"
Application.Run "Macro8"
Application.Run "Macro8"
Application.Run "Macro8"
Application.Run "Macro8"
Application.Run "Macro8"
Application.Run "Macro8"
Application.Run "Macro8"
Application.Run "Macro8"
Application.Run "Macro8"
Application.Run "Macro8"
Application.Run "Macro8"
Application.Run "Macro8"
Application.Run "Macro8"
Application.Run "Macro8"
Application.Run "Macro8"
Application.Run "Macro8"
Application.Run "Macro8"
Application.Run "Macro8"
Application.Run "Macro8"
Application.Run "Macro8"
Application.Run "Macro8"
Application.Run "Macro8"
Application.Run "Macro8"
Application.Run "Macro8"
Application.Run "Macro8"
Application.Run "Macro8"
Application.Run "Macro8"
Application.Run "Macro8"
Application.Run "Macro8"
Application.Run "Macro8"
End Sub

Posted by Tim Francis-Wright on December 20, 2000 2:30 PM

Try this:-
I'm not sure how much of the row should be
colored, so choose your with statement as needed:

Private Sub CommandButton1_Click()
Dim cel As Object

For Each cel In Range("ATD").Columns(1).Cells
If cel < 1 Then
With Intersect(cel.EntireRow, ActiveSheet.UsedRange).Interior ' within UsedRange
' With cel.EntireRow.Interior ' whole row
.ColorIndex = 8
.Pattern = xlSolid
End With
End If
Next cel
End Sub

Posted by Tillymint on December 21, 2000 3:00 PM

This worked a trat thank you so much. You have given me the best Christmas present. Hope you have a Merry Christmas and a happy New Year.