Conditional Formatting with VBA
 

I though this would be easy, but I can't get it right, any help would be
greatly appreciated.

I'm trying to go down Column "D" (about 250 rows) and when the contents of a
cell = "HOURS" go to each cell to the right (31 cells) and if the contents of
that cell = 9 then colour(color) the font red.
Then continue down Col "D" and so on.

I can get the Macro to get to the cell containing "HOURS", but after that
nothing works, so I'm stumped.

Eventually I will have many more conditions that just the one above, so I
can't use Conditional Formatting, and in fact I don't want to.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200805/1

Conditional Formatting with VBA
 

Here you go, something I just knocked up, hopefully it'll work

Public Sub TimmysTool()
Dim j As Long
Dim i As Long
Dim colSize As Long
Dim rowLength As Long

colSize = Sheets("Sheet1").Cells(Rows.Count, 4).End(xlUp).Row

For i = 1 To colSize
If Sheets("Sheet1").Cells(i, 4).Value = "HOURS" Then
rowLength = Sheets("Sheet1").Cells(i,
Columns.Count).End(xlToLeft).Column
j = 4
While j <= rowLength
If Sheets("Sheet1").Cells(i, j).Value = 9 Then
With Sheets("Sheet1").Cells(i, j)
.Font.Color = RGB(255, 0, 0)
End With
End If
j = j + 1
Wend
End If
Next i
End Sub

"Francois via OfficeKB.com" wrote:

I though this would be easy, but I can't get it right, any help would be
greatly appreciated.

I'm trying to go down Column "D" (about 250 rows) and when the contents of a
cell = "HOURS" go to each cell to the right (31 cells) and if the contents of
that cell = 9 then colour(color) the font red.
Then continue down Col "D" and so on.

I can get the Macro to get to the cell containing "HOURS", but after that
nothing works, so I'm stumped.

Eventually I will have many more conditions that just the one above, so I
can't use Conditional Formatting, and in fact I don't want to.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200805/1

Conditional Formatting with VBA
 

NateBuckley wrote:
Here you go, something I just knocked up, hopefully it'll work


Thank for the very quick reply, I'll give it a go

--
Message posted via http://www.officekb.com

Conditional Formatting with VBA
 

Maybe this

Right click your sheet tab, view code and paste it in

Sub stantial()
lastrow = Cells(Cells.Rows.Count, "D").End(xlUp).Row
Set myrange = Range("D1:D" & lastrow)
For Each C In myrange
If UCase(C.Value) = "HOURS" Then
For x = 5 To 36
If Cells(C.Row, x).Value = 9 Then
Cells(C.Row, x).Interior.ColorIndex = 3
End If
Next
End If
Next
End Sub

Mike

"Francois via OfficeKB.com" wrote:

I though this would be easy, but I can't get it right, any help would be
greatly appreciated.

I'm trying to go down Column "D" (about 250 rows) and when the contents of a
cell = "HOURS" go to each cell to the right (31 cells) and if the contents of
that cell = 9 then colour(color) the font red.
Then continue down Col "D" and so on.

I can get the Macro to get to the cell containing "HOURS", but after that
nothing works, so I'm stumped.

Eventually I will have many more conditions that just the one above, so I
can't use Conditional Formatting, and in fact I don't want to.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200805/1

Conditional Formatting with VBA
 

Francois wrote:
Here you go, something I just knocked up, hopefully it'll work

Thank for the very quick reply, I'll give it a go


Spot on !!!

Thanks a lot

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200805/1

Conditional Formatting with VBA
 

you wanted font so use this line

Cells(C.Row, x).Font.ColorIndex = 3

Mike

"Francois via OfficeKB.com" wrote:

I though this would be easy, but I can't get it right, any help would be
greatly appreciated.

I'm trying to go down Column "D" (about 250 rows) and when the contents of a
cell = "HOURS" go to each cell to the right (31 cells) and if the contents of
that cell = 9 then colour(color) the font red.
Then continue down Col "D" and so on.

I can get the Macro to get to the cell containing "HOURS", but after that
nothing works, so I'm stumped.

Eventually I will have many more conditions that just the one above, so I
can't use Conditional Formatting, and in fact I don't want to.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200805/1