Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I convert conditional formatting into explicit formatting? | Excel Discussion (Misc queries) | |||
Conditional formatting--different formatting depending on cell con | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |