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


--
nockam
------------------------------------------------------------------------
nockam's Profile: http://www.excelforum.com/member.php...o&userid=15744
View this thread: http://www.excelforum.com/showthread...hreadid=556271

'-----------------------------------------------------------------
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" wrote in
message ...

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

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


Bob Phillips Wrote:
'-----------------------------------------------------------------
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" wrote
in
message ...

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



--
chris46521
------------------------------------------------------------------------
chris46521's Profile: http://www.excelforum.com/member.php...o&userid=35909
View this thread: http://www.excelforum.com/showthread...hreadid=556271

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

'-----------------------------------------------------------------
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" wrote in
message ...

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..


--
titch
------------------------------------------------------------------------
titch's Profile: http://www.excelforum.com/member.php...o&userid=31190
View this thread: http://www.excelforum.com/showthread...hreadid=556271

'-----------------------------------------------------------------
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" wrote in
message ...

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

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