Automatically Change Cell Color When Value Entered

[ir121973]

Hi,

I wonder whether someone may be able to help me please.

I am using the code below to automatically change the cell background colour once a value has been entered.

Private Sub Worksheet_Change (ByVal as Target As Range)
Set MyRange = Range ("D4:O25")
For Each Cell In MyRange

If Cell.Value >=1 Then
Cell.Interior.ColorIndex=35
End If

If Cell. Value < 1 Then
Cell.Interiot.ColorIndex=XLNone
End If

Next
End Sub

This works fine, but what I would like it to do, is for it to not only look and see whether a value has been entered into the cell, but also to look in the Range C4:C25 and look for the name Chris, so basically it will only shade in the cell if it finds "Chris" in my column C Range and a value in the applicable cell.

I can get this to work by adding another If statement into the first but it then shades the cells in column C as well.

I'm pretty new to Vb and I'm sure it's quite simple, but I just wondered whether someone could point me in the right direction please.

Many thanks

Chris

 

[VoG]

Hi. Try this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range, Cell As Range
Set MyRange = Range("D4:O25")
For Each Cell In MyRange
    If Cell.Value >= 1 And Range("C" & Cell.Row).Value = "Chris" Then
        Cell.Interior.ColorIndex = 35
    Else
        Cell.Interior.ColorIndex = xlNone
    End If
Next Cell
End Sub

 

[Andrew Poulsom]

Can't you just apply this Conditional Formatting formula to D4:O25:

=AND($C4="Chris",D4>0)

with the colour you want?

 

[ir121973]

Hi,

Many thanks for getting back to me.

I had thought about using Conditional Formatting, but I must admit I suppose I took the longer route because I'm interested in learning more about VB so I just wanted to expand my knowledge a little more. I also wasn't sure whether because I wanted to look for the name within a range whether Conditional Formatting may have been a little cumbersome?

Regards

Chris