More than 6 conditional Formats....VBA Coding Advise please

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dermot - 06 Jan 2006 01:06 GMT

If I have a range of cells A1:M20
Column K has a validation list with 6 Options....Yes, No, W, X, Y, Z

The conditional formatting is limited to 3 conditions.

Can anyone provide code that would permit the colour of the cells in each
row in the range A1 to M20 when the appropriate option is selected from the
relevant cell in column K.

I.E. I want Row range A1:M1 cells to change to say Yellow when "Yes"
selected from the list in K1 ......or say red if "No" ( and other colours
for W,X Y,Z)

An example of the coding would be appreciated , if it's possible?
Thanks

Reply to this Message

keithl816 - 06 Jan 2006 03:03 GMT

Take a look at this link

http://www.excelforum.com/showthread.php?t=497871

Signature

keithl816

Reply to this Message

Dermot - 06 Jan 2006 18:16 GMT

Thanks for the link Keith.
Interesting stuff
Thanks

> Take a look at this link
>
> http://www.excelforum.com/showthread.php?t=497871

Reply to this Message

JulieD - 06 Jan 2006 03:09 GMT

Hi Dermot

If i understand your question correctly then the following code should do
what you want (you'll have to edit the colours as required).
--------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Err_Handler

If Not Intersect(Target, Range("K1:K20")) Is Nothing Then
Application.EnableEvents = False
i = Target.Row
Select Case Target.Value
Case "Yes"
Range("A" & i & ":M" & i).Interior.ColorIndex = 6
Case "No"
Range("A" & i & ":M" & i).Interior.ColorIndex = 12
Case "W"
Range("A" & i & ":M" & i).Interior.ColorIndex = 18
Case "X"
Range("A" & i & ":M" & i).Interior.ColorIndex = 22
Case "Y"
Range("A" & i & ":M" & i).Interior.ColorIndex = 26
Case "Z"
Range("A" & i & ":M" & i).Interior.ColorIndex = 30
Case Else
Range("A" & i & ":M" & i).Interior.ColorIndex = xlNone
End Select
End If

Err_Handler:
Application.EnableEvents = True
End Sub

--
note, this needs to go on the sheet module of where your range is (right
mouse click on the appropriate sheet tab and choose view / code ... copy &
paste the code in there).

hope this helps.

Signature

Cheers
JulieD

julied_ng at hctsReMoVeThIs dot net dot au

> If I have a range of cells A1:M20
> Column K has a validation list with 6 Options....Yes, No, W, X, Y, Z
[quoted text clipped - 11 lines]
> An example of the coding would be appreciated , if it's possible?
> Thanks

Reply to this Message

Dermot - 06 Jan 2006 17:51 GMT

Hi Julie,
This is great.
I had found a case select code else where, but wasn't too sure how to edit
to the "Yes" etc conditions that I would like to use.
I am reading through the Susann Novalis VBA book which is great, but this
code is more advanced than the level I am at.
Can you suggest any other VBA learning sources for Excel?
Thanks again
Dermot

> Hi Dermot
>
[quoted text clipped - 51 lines]
> > An example of the coding would be appreciated , if it's possible?
> > Thanks

Reply to this Message

Dermot - 08 Jan 2006 21:20 GMT

Hi Julie,
What line of code would I add to remove the colour formatting to it's
original blank appearance with no text within a row.......assumming I
included an option in the validation list "Undo"?
Thanks
Dermot

> Hi Dermot
>
[quoted text clipped - 51 lines]
> > An example of the coding would be appreciated , if it's possible?
> > Thanks

Reply to this Message

R.VENKATARAMAN - 09 Jan 2006 02:46 GMT

foramt menu-conditional formatting-delete-option 1-ok

> Hi Julie,
> What line of code would I add to remove the colour formatting to it's
[quoted text clipped - 64 lines]
> > > An example of the coding would be appreciated , if it's possible?
> > > Thanks

Reply to this Message

Dermot - 09 Jan 2006 09:30 GMT

Thanks for the reply R.VENKATARAMAN
Please advise....
Wouldn't this just delete conditional formating condition 1?
I am looking to include an option in the validation list to clear the
formatting carried out by the code......I don't want to pemanently remove any
formatting.
Thanks

"" wrote:

> foramt menu-conditional formatting-delete-option 1-ok
>
[quoted text clipped - 71 lines]
> > > > An example of the coding would be appreciated , if it's possible?
> > > > Thanks

Reply to this Message

R.VENKATARAMAN - 09 Jan 2006 12:38 GMT

iam avaguely thinking
you can have two command buttons
one will have the code

Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="5"
Selection.FormatConditions(1).Font.ColorIndex = 46

anohter button will have the code

Selection.FormatConditions.Delete

try somethin like that . insted of command buttons you can even have two
option button

> Thanks for the reply R.VENKATARAMAN
> Please advise....
[quoted text clipped - 42 lines]
> > > > Case Else
> > > > Range("A" & i & ":M" & i).Interior.ColorIndex =

xlNone

> > > > End Select
> > > > End If
[quoted text clipped - 35 lines]
> > > > > An example of the coding would be appreciated , if it's possible?
> > > > > Thanks

Reply to this Message