If cell equals "X", format question?

[rnewsome]

So here's what I'm trying to accomplish:

If a cell in column D (D4) is populated, then the current date populates in the corresponding J column. I would also like it if this also made the whole row text grey.

Is this possible??

 

[snerz]

rnewsome

In C4 type =if($D4="","",today())

Copy this to sells E4 and F4

As for making the cells grey, you can do this by conditional formatting

Senrz

 

[rnewsome]

Thanks snerz,

I appreciate the formula for the date, but as for the conditional format. I'm not sure if there is a conditional format for a whole row based upon a cell equaling "X".

 

[stnkynts]

It sounds like you want a date stamp rather than a constantly updating date. Try this in the worksheet.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D2", "D" & Rows.Count)) Is Nothing Then
    With Target(1, 7)
        .Value = Date
        .EntireRow.Interior.ColorIndex = 15
    End With
End If
End Sub

 

[stnkynts]

I just noticed that you asked for if the value in column D = X.
Try:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Intersect(Target, Range("D1", "D" & Rows.Count)) = "X" Then
    With Target(1, 7)
        .Value = Date
        .EntireRow.Interior.ColorIndex = 15
    End With
End If
End Sub

 

[rnewsome]

stnkynts

That worked great! But is there a way specify a range of cells within a row to change (instead of formating the whole row, format cells B:J). And is there a way for the grey format to change back if "D" isn't populated??

 

[Scott Huish]

Um, you can use Conditional Formatting for this:

Highlight the entire area you want formatted, and use the following Conditional Formatting formula:

=$D1="X"

 

[rnewsome]

Okay so I understand the conditional formatting. But now I just need a code for just a date stamp:

If a cell in the E column equals "x", then the corresponding cell in K column gets date stamp.

 

[stnkynts]

I thought you wanted "X" in column D with the date stamp in J. Or is it now in column E with date stamp in K? Use this for date stamp. I'll write the code for you again. A little modification this time.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "X" Then
    If Not Intersect(Target, Range("E1", "E100")) Is Nothing Then
        Target.Offset(0, 7).Value = Date
    End If
End If
End Sub