Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Fill Color Red
Hi David
Thanks for your replies I've applied both the formulas with no luck Hope I can be more clear with this post, I have dug out an old post off a floppy disk from a couple of years ago. I was working on a similar task when I send a post and had replies from Patrick Molloy, which work ok. I have put another example and the code below. R AA AC 1 1 JOHN < this cell ( Would be Fill colour Red because AA4 = 1 2 2 JANE < this cell Would Not be red 3 0 JANE < this cell Would Not be red 4 1 JOHN < this cell Would Not be red because AA5 is not a number 1 5 0 JOHN < this cell ( Would be Fill colour Red because AA7 = 1 6 5 JANE < this cell ( Would be Fill colour Red because AA8 = 1 7 1 JOHN < this cell Would Not be red 8 1 JANE < this cell Would Not be red I have tried this code it works but it works up the columns, maybe then I was entering new data at the top of the sheet. I would like to work down the sheet and change the Font color to Fill color red. Sub Fillred() Dim aText() As String Dim pointer As Long Dim i As Long Dim rw As Long Dim clText As String Dim clVal As String clText = "AC" clVal = "AA" rw = 4 Do Until Cells(rw, clVal) = "" If pointer 0 Then For i = 1 To UBound(aText) If aText(i) = Cells(rw, clText) Then Cells(rw, clText).Font.Color = vbRed aText(i) = "" Exit For End If Next End If If Cells(rw, clVal) = 1 Then pointer = pointer + 1 ReDim Preserve aText(1 To pointer) aText(pointer) = Cells(rw, clText) End If rw = rw + 1 Loop End Sub Thanks in Advance Dave |
#2
|
|||
|
|||
Dave,
How about this? Sub Test() Sub Test() Dim iLastRow As Long Dim i As Long Dim cell As Range Const kCol As String = "AC" iLastRow = Cells(Rows.Count, kCol).End(xlUp).Row For i = 1 To iLastRow - 1 Cells(i, kCol).Interior.ColorIndex = xlColorIndexNone On Error Resume Next Set cell = Nothing Set cell = Range(kCol & i & ":" & kCol & iLastRow).Find(Cells(i, kCol).Value) On Error GoTo 0 If Not cell Is Nothing Then If cell.Offset(0, -2).Value = 1 And cell.Address < Cells(i, kCol).Address Then Cells(i, kCol).Interior.ColorIndex = 3 End If End If Next i End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Dave" wrote in message ... Hi David Thanks for your replies I've applied both the formulas with no luck Hope I can be more clear with this post, I have dug out an old post off a floppy disk from a couple of years ago. I was working on a similar task when I send a post and had replies from Patrick Molloy, which work ok. I have put another example and the code below. R AA AC 1 1 JOHN < this cell ( Would be Fill colour Red because AA4 = 1 2 2 JANE < this cell Would Not be red 3 0 JANE < this cell Would Not be red 4 1 JOHN < this cell Would Not be red because AA5 is not a number 1 5 0 JOHN < this cell ( Would be Fill colour Red because AA7 = 1 6 5 JANE < this cell ( Would be Fill colour Red because AA8 = 1 7 1 JOHN < this cell Would Not be red 8 1 JANE < this cell Would Not be red I have tried this code it works but it works up the columns, maybe then I was entering new data at the top of the sheet. I would like to work down the sheet and change the Font color to Fill color red. Sub Fillred() Dim aText() As String Dim pointer As Long Dim i As Long Dim rw As Long Dim clText As String Dim clVal As String clText = "AC" clVal = "AA" rw = 4 Do Until Cells(rw, clVal) = "" If pointer 0 Then For i = 1 To UBound(aText) If aText(i) = Cells(rw, clText) Then Cells(rw, clText).Font.Color = vbRed aText(i) = "" Exit For End If Next End If If Cells(rw, clVal) = 1 Then pointer = pointer + 1 ReDim Preserve aText(1 To pointer) aText(pointer) = Cells(rw, clText) End If rw = rw + 1 Loop End Sub Thanks in Advance Dave |
#3
|
|||
|
|||
Hi Bob
My post was not suppose to go here, but under my first post I haven't used newsgroups for a while. I apologize to David McRitchie for my error. Yes it worked a treat Bob, Thanks Thanks to the both of you. "Bob Phillips" wrote in message ... Dave, How about this? Sub Test() Sub Test() Dim iLastRow As Long Dim i As Long Dim cell As Range Const kCol As String = "AC" iLastRow = Cells(Rows.Count, kCol).End(xlUp).Row For i = 1 To iLastRow - 1 Cells(i, kCol).Interior.ColorIndex = xlColorIndexNone On Error Resume Next Set cell = Nothing Set cell = Range(kCol & i & ":" & kCol & iLastRow).Find(Cells(i, kCol).Value) On Error GoTo 0 If Not cell Is Nothing Then If cell.Offset(0, -2).Value = 1 And cell.Address < Cells(i, kCol).Address Then Cells(i, kCol).Interior.ColorIndex = 3 End If End If Next i End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Dave" wrote in message ... Hi David Thanks for your replies I've applied both the formulas with no luck Hope I can be more clear with this post, I have dug out an old post off a floppy disk from a couple of years ago. I was working on a similar task when I send a post and had replies from Patrick Molloy, which work ok. I have put another example and the code below. R AA AC 1 1 JOHN < this cell ( Would be Fill colour Red because AA4 = 1 2 2 JANE < this cell Would Not be red 3 0 JANE < this cell Would Not be red 4 1 JOHN < this cell Would Not be red because AA5 is not a number 1 5 0 JOHN < this cell ( Would be Fill colour Red because AA7 = 1 6 5 JANE < this cell ( Would be Fill colour Red because AA8 = 1 7 1 JOHN < this cell Would Not be red 8 1 JANE < this cell Would Not be red I have tried this code it works but it works up the columns, maybe then I was entering new data at the top of the sheet. I would like to work down the sheet and change the Font color to Fill color red. Sub Fillred() Dim aText() As String Dim pointer As Long Dim i As Long Dim rw As Long Dim clText As String Dim clVal As String clText = "AC" clVal = "AA" rw = 4 Do Until Cells(rw, clVal) = "" If pointer 0 Then For i = 1 To UBound(aText) If aText(i) = Cells(rw, clText) Then Cells(rw, clText).Font.Color = vbRed aText(i) = "" Exit For End If Next End If If Cells(rw, clVal) = 1 Then pointer = pointer + 1 ReDim Preserve aText(1 To pointer) aText(pointer) = Cells(rw, clText) End If rw = rw + 1 Loop End Sub Thanks in Advance Dave |
#4
|
|||
|
|||
You got the answer you were looking for, which is good, but
your example data shows for Row 1 you are looking at row 4 (row 1 + 3) for row 5 you are looking at row 6 instead of row 8 for row 6 you are looking at row 8 instead of row 9 and for conditional formatting formulas to work they have to be a formula and I fail to see the connection between varying row differences. -- --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Dave" wrote in message ... Hi Bob My post was not suppose to go here, but under my first post I haven't used newsgroups for a while. I apologize to David McRitchie for my error. Yes it worked a treat Bob, Thanks Thanks to the both of you. "Bob Phillips" wrote in message ... Dave, How about this? Sub Test() Sub Test() Dim iLastRow As Long Dim i As Long Dim cell As Range Const kCol As String = "AC" iLastRow = Cells(Rows.Count, kCol).End(xlUp).Row For i = 1 To iLastRow - 1 Cells(i, kCol).Interior.ColorIndex = xlColorIndexNone On Error Resume Next Set cell = Nothing Set cell = Range(kCol & i & ":" & kCol & iLastRow).Find(Cells(i, kCol).Value) On Error GoTo 0 If Not cell Is Nothing Then If cell.Offset(0, -2).Value = 1 And cell.Address < Cells(i, kCol).Address Then Cells(i, kCol).Interior.ColorIndex = 3 End If End If Next i End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Dave" wrote in message ... Hi David Thanks for your replies I've applied both the formulas with no luck Hope I can be more clear with this post, I have dug out an old post off a floppy disk from a couple of years ago. I was working on a similar task when I send a post and had replies from Patrick Molloy, which work ok. I have put another example and the code below. R AA AC 1 1 JOHN < this cell ( Would be Fill colour Red because AA4 = 1 2 2 JANE < this cell Would Not be red 3 0 JANE < this cell Would Not be red 4 1 JOHN < this cell Would Not be red because AA5 is not a number 1 5 0 JOHN < this cell ( Would be Fill colour Red because AA7 = 1 6 5 JANE < this cell ( Would be Fill colour Red because AA8 = 1 7 1 JOHN < this cell Would Not be red 8 1 JANE < this cell Would Not be red I have tried this code it works but it works up the columns, maybe then I was entering new data at the top of the sheet. I would like to work down the sheet and change the Font color to Fill color red. Sub Fillred() Dim aText() As String Dim pointer As Long Dim i As Long Dim rw As Long Dim clText As String Dim clVal As String clText = "AC" clVal = "AA" rw = 4 Do Until Cells(rw, clVal) = "" If pointer 0 Then For i = 1 To UBound(aText) If aText(i) = Cells(rw, clText) Then Cells(rw, clText).Font.Color = vbRed aText(i) = "" Exit For End If Next End If If Cells(rw, clVal) = 1 Then pointer = pointer + 1 ReDim Preserve aText(1 To pointer) aText(pointer) = Cells(rw, clText) End If rw = rw + 1 Loop End Sub Thanks in Advance Dave |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fill colour red | Excel Discussion (Misc queries) | |||
Auto Fill Options | Excel Discussion (Misc queries) | |||
fill series grayed out (not available, disactivated) | Excel Discussion (Misc queries) | |||
Identifying the Active Fill Color | Excel Discussion (Misc queries) | |||
3-d reference not adjusting when using Fill Handle to copy down | Excel Worksheet Functions |