I am trying to come up with a way through VBA to conditionally format a row based on one cell within that row. I can do this just fine through conditional formatting with 1-3 criterias but i need to do it for 5. Can anyone help me out? I need to say something like if cell A1 = This then color row 1 ____
Thanks,
Garrett
'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "A1"
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Entirerow.Interior.ColorIndex = 3 'red
Case 2: .Entirerow.Interior.ColorIndex = 6 'yellow
Case 3: .Entirerow.Interior.ColorIndex = 5 'blue
Case 4: .Entirerow.Interior.ColorIndex = 10 'green
End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"nockam" <[email protected]> wrote in
message news:[email protected]...
>
> I am trying to come up with a way through VBA to conditionally format a
> row based on one cell within that row. I can do this just fine through
> conditional formatting with 1-3 criterias but i need to do it for 5.
> Can anyone help me out? I need to say something like if cell A1 = This
> then color row 1 ____
>
> Thanks,
>
> Garrett
>
>
> --
> nockam
> ------------------------------------------------------------------------
> nockam's Profile:
http://www.excelforum.com/member.php...o&userid=15744
> View this thread: http://www.excelforum.com/showthread...hreadid=556271
>
See if posts like this one may help you. Using the Select Case or If
ElseIf statements can work for this.
http://groups.google.com/group/micro...10e43923f1e09e
Mark
nockam wrote:
> I am trying to come up with a way through VBA to conditionally format a
> row based on one cell within that row. I can do this just fine through
> conditional formatting with 1-3 criterias but i need to do it for 5.
> Can anyone help me out? I need to say something like if cell A1 = This
> then color row 1 ____
>
> Thanks,
>
> Garrett
>
>
> --
> nockam
> ------------------------------------------------------------------------
> nockam's Profile: http://www.excelforum.com/member.php...o&userid=15744
> View this thread: http://www.excelforum.com/showthread...hreadid=556271
Bob you saved my life! This works perfectly thank you for your help. I have been trying to figure this out all day then i finally gave up and asked for help. thanks again, garrett
Similar Problem
I would like to use VBA to conditionally format based on various different cells values with in my Table.
TAble Range is B16:X150ish
An example of what I want is:
IF a cell in C16:C150 <> "" then that row should = blue
but then if the Corrisponding cell in Column V <>"" then remove formatting.
Another would be:
If a cell in Column B = "PRE" and the the Date/Time in column U is > than NOW then the row should be Green.
Im not to great with VBA so some sort of explanation of what the code is doing would be greatly appreciated to.
Thanks
Titch
'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "B:B,C:C, U:U,V:V"
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
'and column C not empty and column V is empty, clear it, else
blue
If Me.Cells(.Row, "C").Value <> "" And _
Me.Cells(.Row, "V").Value = "" Then
.EntireRow.Interior.ColorIndex = 5 'blue
Else
.EntireRow.Interior.ColorIndex = xlColorIndexNone
End If
'column B = "PRE" and column U greater than NOW
If Me.Cells(.Row, "B").Value = "PRE" And Me.Cells(.Row,
"U").Value > Now Then
.EntireRow.Interior.ColorIndex = 10 'green
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"titch" <[email protected]> wrote in
message news:[email protected]...
>
> I would like to use VBA to conditionally format based on various
> different cells values with in my Table.
>
> TAble Range is B16:X150ish
>
> An example of what I want is:
>
> IF a cell in C16:C150 <> "" then that row should = blue
> but then if the Corrisponding cell in Column V <>"" then remove
> formatting.
>
> Another would be:
> If a cell in Column B = "PRE" and the the Date/Time in column U is >
> than NOW then the row should be Green.
>
> Im not to great with VBA so some sort of explanation of what the code
> is doing would be greatly appreciated to.
>
> Thanks
>
> Titch
>
>
> --
> titch
> ------------------------------------------------------------------------
> titch's Profile:
http://www.excelforum.com/member.php...o&userid=31190
> View this thread: http://www.excelforum.com/showthread...hreadid=556271
>
Thanks that works a treat. But how about if I wanted to just color a selectrange to change color rather than the entire row i.e. just the cells along that row with in the table, say B:AB.
Can I just modify that bit of code or is it more complex than that?
Again I thank you..
'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "B:B,C:C, U:U,V:V"
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
'column C not empty and column V is, clear it, else blue
If Me.Cells(.Row, "C").Value <> "" And _
Me.Cells(.Row, "V").Value = "" Then
Me.Cells(.Row, "B").Resize(, 26).Interior.ColorIndex = 5
'blue
Else
.EntireRow.Interior.ColorIndex = xlColorIndexNone
End If
'column B = "PRE" and column U greater than NOW
If Me.Cells(.Row, "B").Value = "PRE" And _
Me.Cells(.Row, "U").Value > Now Then
Me.Cells(.Row, "B").Resize(, 26).Interior.ColorIndex = 10
'green
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"titch" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks that works a treat. But how about if I wanted to just color a
> selectrange to change color rather than the entire row i.e. just the
> cells along that row with in the table, say B:AB.
> Can I just modify that bit of code or is it more complex than that?
>
> Again I thank you..
>
>
> --
> titch
> ------------------------------------------------------------------------
> titch's Profile:
http://www.excelforum.com/member.php...o&userid=31190
> View this thread: http://www.excelforum.com/showthread...hreadid=556271
>
Thanks Again!!
Ok that is more like it, but I clearly didn't think this through so appoligise for not getting it right myself the first time. After applying this to the said spreadsheet I realised I need it to only apply to those rows below say 13.
How would I go about this. Again I say thank you!
'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "B:B,C:C, U:U,V:V"
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Row > 13 Then
'column C not empty and column V is, clear it, else blue
If Me.Cells(.Row, "C").Value <> "" And _
Me.Cells(.Row, "V").Value = "" Then
Me.Cells(.Row, "B").Resize(, 26).Interior.ColorIndex = 5
Else
.EntireRow.Interior.ColorIndex = xlColorIndexNone
End If
'column B = "PRE" and column U greater than NOW
If Me.Cells(.Row, "B").Value = "PRE" And _
Me.Cells(.Row, "U").Value > Now Then
Me.Cells(.Row, "B").Resize(, 26).Interior.ColorIndex = 10
End If
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"titch" <[email protected]> wrote in
message news:[email protected]...
>
> Ok that is more like it, but I clearly didn't think this through so
> appoligise for not getting it right myself the first time. After
> applying this to the said spreadsheet I realised I need it to only
> apply to those rows below say 13.
> How would I go about this. Again I say thank you!
>
>
> --
> titch
> ------------------------------------------------------------------------
> titch's Profile:
http://www.excelforum.com/member.php...o&userid=31190
> View this thread: http://www.excelforum.com/showthread...hreadid=556271
>
I have been having a problem along the same lines, but I can’t get the code posted here to work and I don’t know VBA at all. I would like a range of cells in a row to be filled in a color based on the alphanumeric value of cell M within that row. There are 10 different inputs for cell M and thus ten different row fill colors. I tried modifying the code posted here but it didn’t work. Any help would be greatly appreciated.
Thanks, Chris
Originally Posted by Bob Phillips
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks