Posted to microsoft.public.excel.programming
|
|
Visual Basic programming in Excel for conditional formatting
But if it was only specific cells why not test just them, as in an earlier
version.
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"Doo0592" wrote in message
oups.com...
Yeah, I see what you mean. However, the conditions are picked from a
drop down list and are specific text strings so the chances of an end
user entering them in another cell should be minimal. But you know them
if they can bugger it up they will! Lol. I'll see how it goes, if
someone brings it up I'll change it but we spent so long figuring it
out I think I need to give it a rest for today. It's almost time for me
to go home :) Yay!
Bob Phillips wrote:
I think this is what you mean
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Rows("3:500")) Is Nothing Then
With Target
Select Case .Value
Case "CONDITION 1":
Cells(.Row, 2).Resize(, 23).Interior.ColorIndex = 15
Case "CONDITION 2":
Cells(.Row, 2).Resize(, 23).Interior.ColorIndex = 35
Case "", "CONDITION 3":
Cells(.Row, 2).Resize(, 23).Interior.ColorIndex = 0
Case "CONDITION 4":
Cells(.Row, 2).Resize(, 23).Interior.ColorIndex = 0
Case "CONDITION 5":
Cells(.Row, 2).Resize(, 23).Interior.ColorIndex = 0
End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
It might even be
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Rows("3:500")) Is Nothing Then
With Target
Select Case .Value
Case "CONDITION 1":
Cells(.Row, 2).Resize(, 23).Interior.ColorIndex = 15
Case "CONDITION 2":
Cells(.Row, 2).Resize(, 23).Interior.ColorIndex = 35
Case Else:
Cells(.Row, 2).Resize(, 23).Interior.ColorIndex = 0
End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
The thing that worreis me about this is that it will paint B:X if
CONDITION
1 is entered anywhere within the row. Seems a little odd.
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"Doo0592" wrote in message
oups.com...
Ok... here's a quick explaination of what I am trying to achieve:
I work with cases that I have to track (sorry bout taking out the
actual conditions but this is for my work). L3 is the status of the
case. M3 is the date that the next action should be taken. I have the
cases hightlighting in green and grey on certain conditions through
the
VB code and I have used Excel's conditional formatting box to higlight
the date. This works fine now apart from the conflict between using VB
code and Excel CF (not changing the cells back to normal if conditions
change). I will work this problem later I think.
This is the code I have for excuting the conditions on one row.
Private Sub Worksheet_Change(ByVal Target As Range)
Const ws_range As String = ("L3:L500")
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Rows("3:500")) Is Nothing Then
With Target
Select Case .Value
Case "CONDITION 1": Range("B3:X3").Interior.ColorIndex
= 15
Case "CONDITION 2": Range("B3:X3").Interior.ColorIndex
= 35
Case "", "CONDITION 3":
Range("B3:X3").Interior.ColorIndex = 0
Case "CONDITION 4": Range("B3:X3").Interior.ColorIndex
= 0
Case "CONDITION 5": Range("B3:X3").Interior.ColorIndex
= 0
End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
You will notice that most of the conditions have "blank" formatting. I
did this just so they would change back to normal if the "status" was
changed.
So this code evaluates the correct rows and I change the contents of
each row in column L but it will only change the interiors of B3:X3...
when if I change the contents of L4 it should change the interior of
B4:X4 etc.. Will I end up with lots of complicated Ifelse statements?
Gulp! :)
Does this make sense?
Bob Phillips wrote:
If you want M3 to clear the CF when you clear its value, but not the
rest of
the row, you need separate CF conditions for them. What do you
currently
have?
When you want VB code (event code I presume)( to restrict its target
range,
use something like the following
If Not Intersect(Target,Rows("3:500")) Is Nothing Then
'do your thing
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"Doo0592" wrote in message
oups.com...
Right,
I think I've cracked it! I gave up trying to write code that
accounts
for all the conditions. Instead I have set Excel's conditional
formatting to evaluate for the date <= today (thanks for the code
to
ignore blanks) and I have used VB to evaluate for the rest. It
seems
to
be working. The only flaw is that if you have entered a true value
in
M3 and L3 and then delete the value in M3 it clears the formatting
for
the whole row... because of the ignore blanks! I think I can live
with
this but if you can think of a way around it I wouldn't say no!
lol
The
last and final thing I need to do is to get the VB code to
evaluate
for
rows 3:500. Can we use a for statement for this and, if so, how do
we
write it?
Ta muchly Bob! :)
Doo0592 wrote:
Hi Bob,
This nearly works but i don't think it's treating the date part
as a
condition to evaluate. As this part runs first the (which is
fine as
long as it doesn't override the second part) it's turning the
cells/
fonts to white and red but they stay that way for the next part.
Is
there a way we can assign the date value to a vairable to use in
the
case statement?
Thanks
Bob Phillips wrote:
I think that the code below is what you want.
As to the .Value, that is just testing the value of the Target
cell.
If you
notice, earlier there is a 'With Target' statement, and so any
dot
statement
(e.g. .Value) will refer to that object.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Address = "$m$3" Then
If IsDate(.Value) Then
If .Value < Date Then
With Range("B3:X3")
.Font.ColorIndex = 2
.Interior.ColorIndex = 3
End With
End If
End If
ElseIf .Address = "$$3" Then
Select Case .Value
Case "condition 1": _
Range("B3:X3").Interior.ColorIndex =
15
Case "condition 2": _
Range("B3:X3").Interior.ColorIndex =
35
Case "", "condition3": _
Range("B3:X3").Interior.ColorIndex = 0
Case "condition 4": _
Range("B3:X3").Interior.ColorIndex = 0
Case "condition 5": _
Range("B3:X3").Interior.ColorIndex = 0
End Select
End If
End With
ws_exit:
Application.EnableEvents = True
End Sub
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing
direct)
"Doo0592" wrote in message
oups.com...
Nearly, I forgot to mention that the date will be entered in
a
different cell from the conditions already set. It will be
in
M3.
Can I
set IsDate to a variable that uses this cell as a reference?
Or
does
the date part have to be a separate procedure. Also, I am a
nooby at
VB
but I used to program a little on the BBC and I know basic
JavaScript
so I understand most of the principles of programming in VB
but
not
all. I almost understand the code you have written but can
you
explain
the .value after IsDate? Is this where I can put my
variable?
Hope
this
makes sense! :) Thanks for your help thou, I've been working
on
this
one for ages :(
Bob Phillips wrote:
Is this what you mean
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "L3"
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B3:X3")) Is Nothing
Then
With Target
If IsDate(.Value) Then
If .Value < Date Then
With Range("B3:X3")
.Font.ColorIndex = 2
.Interior.ColorIndex = 3
End With
End If
Else
Select Case .Value
Case "condition 1": _
Range("B3:X3").Interior.ColorIndex
=
15
Case "condition 2": _
Range("B3:X3").Interior.ColorIndex
=
35
Case "", "condition3": _
Range("B3:X3").Interior.ColorIndex
= 0
Case "condition 4": _
Range("B3:X3").Interior.ColorIndex
= 0
Case "condition 5": _
Range("B3:X3").Interior.ColorIndex
= 0
End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing
direct)
"Doo0592" wrote in message
oups.com...
Hi all,
I am trying to create a spreadsheet that will highlight
certain
conditions when they match. So far I have what is below.
I
need
to add
another case that will highlight the row when a date
passes
(ie,
todays()). I would like it to highlight interior red
with
white
bold
font. And how do i get this code to act on multiple
rows?
Any help appreciated :)
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "L3"
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B3:X3")) Is
Nothing
Then
With Target
Select Case .Value
Case "condition 1":
Range("B3:X3").Interior.ColorIndex
= 15
Case "condition 2":
Range("B3:X3").Interior.ColorIndex
= 35
Case "", "condition3":
Range("B3:X3").Interior.ColorIndex = 0
Case "condition 4":
Range("B3:X3").Interior.ColorIndex
= 0
Case "condition 5":
Range("B3:X3").Interior.ColorIndex
= 0
End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
|