I have used a 3 conditional formats for other things. With vba, I would like
to have a cell fill be red and the font black if the cell equals a date in
cell K7. The range of cells for this possible format is D12:J81

I found some code on the net and tried to modify it to get it to do what I
want ...I made into a disaster.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Range("D12:J81")
If Application.Cells = Today() Then

..Interior.ColorIndex = 3
Else
..Interior.ColorIndex = 0

End If
End With
End Sub

Thanks for any help

ADK

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "D12:J81"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value = Me.Range("K7").Value Then
.Interior.ColorIndex = 3
Else
.Interior.ColorIndex = xlColorIndexNone
End If
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)

"ADK" wrote in message
...
I have used a 3 conditional formats for other things. With vba, I would
like
to have a cell fill be red and the font black if the cell equals a date in
cell K7. The range of cells for this possible format is D12:J81

I found some code on the net and tried to modify it to get it to do what I
want ...I made into a disaster.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Range("D12:J81")
If Application.Cells = Today() Then

.Interior.ColorIndex = 3
Else
.Interior.ColorIndex = 0

End If
End With
End Sub

Thanks for any help

ADK

I followed your directions but get errors:

Compile error:
Ambiguous name detected: Worksheet_Change


I do have another code segment that has the same first line:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("S12:S40")) Is Nothing Then
Target(1).Value = UCase(Target(1).Value)
End If
Application.EnableEvents = True
End Sub

Can you have more than one?

Any idea and fix for the error?

Thanks

-ADK


"Bob Phillips" wrote in message
...
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "D12:J81"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value = Me.Range("K7").Value Then
.Interior.ColorIndex = 3
Else
.Interior.ColorIndex = xlColorIndexNone
End If
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)

"ADK" wrote in message
...
I have used a 3 conditional formats for other things. With vba, I would
like
to have a cell fill be red and the font black if the cell equals a date
in
cell K7. The range of cells for this possible format is D12:J81

I found some code on the net and tried to modify it to get it to do what
I
want ...I made into a disaster.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Range("D12:J81")
If Application.Cells = Today() Then

.Interior.ColorIndex = 3
Else
.Interior.ColorIndex = 0

End If
End With
End Sub

Thanks for any help

ADK

BTW ....if this matters at all, the sheet is protected and I am using Excel
2000




"Bob Phillips" wrote in message
...
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "D12:J81"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value = Me.Range("K7").Value Then
.Interior.ColorIndex = 3
Else
.Interior.ColorIndex = xlColorIndexNone
End If
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)

"ADK" wrote in message
...
I have used a 3 conditional formats for other things. With vba, I would
like
to have a cell fill be red and the font black if the cell equals a date
in
cell K7. The range of cells for this possible format is D12:J81

I found some code on the net and tried to modify it to get it to do what
I
want ...I made into a disaster.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Range("D12:J81")
If Application.Cells = Today() Then

.Interior.ColorIndex = 3
Else
.Interior.ColorIndex = 0

End If
End With
End Sub

Thanks for any help

ADK

No, you can't have more than one, but you can combine them, and unprotect
them

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE_1 As String = "D12:J81"
Const WS_RANGE_2 As String = "S12:S40"

On Error GoTo ws_exit:
Application.EnableEvents = False
Me.Unprotect
If Not Intersect(Target, Me.Range(WS_RANGE_1)) Is Nothing Then
With Target
If .Value = Me.Range("K7").Value Then
.Interior.ColorIndex = 3
Else
.Interior.ColorIndex = xlColorIndexNone
End If
End With
ElseIf Not Application.Intersect(Target, Range(WS_RANGE_2)) Is Nothing
Then
.Cells(1,1).Value = UCase(.Cells(1,1).Value)
End If

ws_exit:
Application.EnableEvents = True
Me.Protect
End Sub

--
HTH

Bob Phillips

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

"Bob Phillips" wrote in message
...
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "D12:J81"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value = Me.Range("K7").Value Then
.Interior.ColorIndex = 3
Else
.Interior.ColorIndex = xlColorIndexNone
End If
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)

"ADK" wrote in message
...
I have used a 3 conditional formats for other things. With vba, I would
like
to have a cell fill be red and the font black if the cell equals a date
in
cell K7. The range of cells for this possible format is D12:J81

I found some code on the net and tried to modify it to get it to do what
I
want ...I made into a disaster.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Range("D12:J81")
If Application.Cells = Today() Then

.Interior.ColorIndex = 3
Else
.Interior.ColorIndex = 0

End If
End With
End Sub

Thanks for any help

ADK

Adding: If I understand your setup, you have conditional formatting in these
cells already. If any of the criteria kicks in, you will se that format, not
the interior that your code (or your manual actions) specifies for the cell.

HTH. Best wishes Harald