Conditional Formatting

[Ken]

Hi Group,
I am trying to format a column's "number" format using CF, but I can't
get the formatting to change. The CF that I have been trying (in Excel
2007 BTW) is:
=Range("P")="ValueA", with the formatting applying to $Q$2:$Q$65000. I
selected one of the Date formats to apply if the condition is true,
but it doesn't work. I can do it easily with VBA
Worksheet_SelectionChange like this, but it kills the "undo" and the
fill handle:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim Value As String
Dim r As Long
r = ActiveCell.Row
With Target
With ActiveCell

'If Row P value is "ValueA", or "ValueB then
'format of Row Q accepts date, otherwise cell format will be "General"
If Cells(r, "P").Value = "ValueA" Or Cells(r, "P").Value = "ValueB"
Then
Cells(r, "Q").NumberFormat = "m/d/yy;@"

ElseIf Cells(r, "P") <> "ValueA" Or Cells(r, "P").Value <> "ValueB"
Then
Cells(r, "Q").NumberFormat = ""


End If
End With
End With
End Sub

I am just trying to preserve the "undo" and fill handle options. Any
suggestions will be greatly appreciated!
Ken

 

[Victor Lobo]

Hi Group,
I am trying to format a column's "number" format using CF, but I can't
get the formatting to change. The CF that I have been trying (in Excel
2007 BTW) is:
=Range("P")="ValueA", with the formatting applying to $Q$2:$Q$65000. I
selected one of the Date formats to apply if the condition is true,
but it doesn't work. I can do it easily with VBA
Worksheet_SelectionChange like this, but it kills the "undo" and the
fill handle:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim Value As String
Dim r As Long
r = ActiveCell.Row
With Target
With ActiveCell

'If Row P value is "ValueA", or "ValueB then
'format of Row Q accepts date, otherwise cell format will be "General"
If Cells(r, "P").Value = "ValueA" Or Cells(r, "P").Value = "ValueB"
Then
Cells(r, "Q").NumberFormat = "m/d/yy;@"

ElseIf Cells(r, "P") <> "ValueA" Or Cells(r, "P").Value <> "ValueB"
Then
Cells(r, "Q").NumberFormat = ""


End If
End With
End With
End Sub

I am just trying to preserve the "undo" and fill handle options. Any
suggestions will be greatly appreciated!
Ken

Hi

Try this

Select $Q$2:$Q$65000

In the "Formula to use box" use this : =OR($P2="ValueA",$P2="ValueB")
Set the Format ("m/d/yy;@")

Hope this helps

 

[Ken]

Thanks, Victor....I think that's going work....I haven't fully tested
it yet (just adding a couple more conditions)....again, thanks so much
for your help!
Ken