Inelegant Solution in Excel 1

[ErikZ]

This is what I was trying to do, if someone enters a date on one worksheet, a cell on another worksheet changes from red to green. If the date is deleted, it goes back from green to red.

The best I could do is to use a formula that puts a 'Y' or a 'N' in the cell. Then use a conditional format to change colors. Everyone else is happy with the results, but I hate hacks. I was hoping there was a way to directly change the color of the cell with a formula.

Does anyone know how to do this?

 

[Databaseguy]

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If IsDate(Range("a1&quot.Value) Then
Range("a2&quot.Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With '
Else
With Selection.Interior
Selection.Interior.ColorIndex = xlNone
End With
End If
End Sub Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[ErikZ]

Hmmm, interesting VB code. It's not a formula though, and once I leave here, no one here will have ANY idea on how to update the spreadsheet if I use VB.

Thanks for the respose, I was wondering how to do it in VB anyways!

 

[SimonDavis]

I think this will work:

On the cell you want to colour, put two conditional format criteria:

1. (formula is) =isblank(datecell)=true

2. (formula is) =isblank(datecell)=false

Where datecell is the location of your date.

Chage the 'cell vaue is' on the conditional format criteria to the 'formula is', and to add the second criteria, click 'add'.

Set the appropriate colour/format for each of the two criteria.

 

[ErikZ]

Doh! All that conditional formatting, and I never looked at the "Cell Value Is" box.

Thanks! I like this method better than what I'm currently using.