Conditional Format Issue

[Guest]

I have a column of calculated results. I have applied conditional formatting
to alternate colours per line, then to change the font colour for values
between 90 and 95% and also to change colour and bold values under 90%. It is
working fine on all but the first three values in the column. I have copied
from the cells below and still get the same issued. I can't see any
difference in the format or properties of these particular cells. Any
thoughts?

 

[Guest]

OK, Answering my own question, it appeard it only works where the background
colour format isn't applied. Is there a way for bot formula and cell value
conditions to be considered?

 

[Bob Phillips]

It is probably relative referencing, but we can tell better if you show us
the CF formula.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

1. formula is: row()=even(row()) - format: pattern/colour blue
2. cell value is: between .9 and .95 - format: font red
3. cell value is: less than .9 - format font red and bold italic

Thx

Dean

 

[Bob Phillips]

Can you explain this bit

.... Is there a way for both formula and cell value conditions to be
considered?


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

It seems once the 'row()=....' condition is met, the subsequent conditions
aren't considered. I want to be able to set conditions based on that formula,
as well as additional conditions based on the cell value.

 

[Bob Phillips]

OK I see. You are right, it stops on a met condition. You need to AND the
conditions to get two at once

=AND(cond1,cond2)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Thanks, but I really need the conditions assessed separately; i.e. regardles
of the cell value, every other cell has the same pattern. I'll post if I
figure a workaround.

 

[Bob Phillips]

I think you will need VBA. Example. Keep the striping CF, and then something
like

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "A1:H10"

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: 'set something
Case 2: 'set something
Case 3: 'set something
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)