conditional font color

[ndendrinos2]

hello to all ... I need either a conditional formatting formula or a VBA maco to do this:
I select colum D and format font color white
I add a conditional format to do this: if in adjacent cells(C3:D1000) have a value then change font color to automatic
Hope I explain it well but again I'm 78 so who knows..... thank you

 

[ndendrinos2]

maybe I can achieve this using VBA ... I've recorded this macro
Sub Macro1()

Range("E10").Select
Selection.AutoFill Destination:=Range("E10:E11"), Type:=xlFillDefault
Range("E10:E11").Select
End Sub

What I need to do is replace E10 by the last populated cell in column E ... copy the cell ... then paste to cell one below in same column

Thank you

 

[ndendrinos2]

Sub Macro2()
Range("E" & Cells.Rows.Count).End(xlUp).Select

End Sub

this will get me to the last populated cell in Column E ..... now I need to drag it down to the next row in the same column
Thanks

 

[kevin9999]

If you want to go down the VBA path, then you probably need a worksheet_change event code. See this on how to add it: Excel VBA: The Magic of the Worksheet Change Event, and code like the following should do what you want:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("C3:D1000"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        For Each c In Range("E3:E1000")
            If WorksheetFunction.CountA(Range(c.Offset(, -2), c.Offset(, -1))) > 0 Then
                c.Font.Color = vbBlack
            Else
                c.Font.Color = vbWhite
            End If
        Next c
        Application.ScreenUpdating = True
    End If
End Sub

 

[ndendrinos2]

Bless you Kevin it works ... will name my first born after you

 

[kevin9999]

I'm 78 so who knows

You're leaving it a bit late but glad the code worked for you

 

[ndendrinos2]

Cheers Kevin.... better late than never