Using formatting for criteria

[Dowsey1977]

Hi,In cells C5, D5, E5 I have some text which are used to give a score to a piece of work, i.e. C5 = Simple, D5 = Medium and E5 = Complex.The way this currently works is fairly basic, but I'm not sure the users want to change the processes. The user would simply highlight one of the cells to indicate the complexity of the piece of work they are doing. What I am aiming to do is allocate a score based on which cell is highlighted, and put a 1,2 or 3 into cell G5. I'd also like to add in some limitation so that only 1 of the 3 cells can highlighted at any time. So if C5 is highlighted, I then go and highlight E5 and C5 becomes 'unhighlighted'.Any ideas if any of this is possible??

[Bob Phillips]

[vba]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "C5:E5"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Address
Case "$C$5": Me.Range("G5").Value = 1
Case "$D$5": Me.Range("G5").Value = 2
Case "$E$5": Me.Range("G5").Value = 3
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

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.

[Dowsey1977]

Excellent...many thanks for this.Is there anyway to add the number to G5 when the formatting of the internal colour changes, as opposed to just selecting the cell??

[Bob Phillips]

A colour change does not trigger any event, so in short, no.

[Dowsey1977]

Oh well, was worth a try.Thanks for the help though.

[mdmackillop]

Is there anyway to add the number to G5 when the formatting of the internal colour changes, as opposed to just selecting the cell??

I don't understand what you're after. What cell is undergoing a colour change, and what is causing it?

[Dowsey1977]

Cell C5,D5 and E5 would be the cells that would be highlighted. But I want the ability to only have 1 cell highlighted, i.e. if C5 is highlighted, and then I highlight D5, C5 becomes unhighlighted.Then, dependant on what cell is highlighted, either a 1,2 or 3 is added to G5.

[mdmackillop]

"Highlight" is a confusing term. If you want to colour a cell, which I'm guessing you are, just say so.
Add these lines after With Target
[VBA]
Range("C5:E5").Interior.ColorIndex = xlNone
Target.Interior.ColorIndex = 6
[/VBA]