View Single Post
  #9   Report Post  
Posted to microsoft.public.excel.newusers
R.VENKATARAMAN
 
Posts: n/a
Default More than 6 conditional Formats....VBA Coding Advise please

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

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



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

"JulieD" wrote:

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.

--
Cheers
JulieD


julied_ng at hctsReMoVeThIs dot net dot au


"Dermot" wrote:

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