Cell colour changes with a formula 1

[jasper451128]

Is there a way to change the colour of one cell based on the input of a different cell. My database needs to have colour coded cells and i have no idea how to do it. Please help.

 

[Loomah]

Stange as this may sound I'm not 100% sure what you're after so here are two possibilities.

If you have data in cells A1 & A2 and a formula in cell A3 eg =A1*A2

1) If you ant to simply highlight the cell with the formula (or either of the other cells for that matter) simply use conditional formatting - Go FORMAT>CONDITIONAL FORMATTING. I can't explain it all here but it is fairly easy to mess around with. This can only be applied to a cell that changes ie if cell A3 becomes >= 10 then apply formatting to A3

2) Second optiion is if you want (for some reason) to highlight A3 if the value input to A1 is greater than 5 then this piece of code would do the trick

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Range("A1") = Range("A1") Then
    If Target > 5 Then
        Range("A3").Interior.ColorIndex = 6
    Else: Range("A3").Interior.ColorIndex = xlColorIndexNone
    End If
End If
End Sub

Beware that if you try to use both methods the conditional formatting appears to take priority over the code!

Good Luck
;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[jasper451128]

Thankyou very much for your help but i have more questions. The first answer you gave is the result i want but i want the colour change to affect a different cell. for your second answer I dont know where to write the code or what bits to change.

The cell that needs changing to red is b4 and the cell that needs to make the colour change is c4. Please could you tailor your answer for these and let me know where i need to put the code please.

Thankyou very much.

 

[Loomah]

Hi
Still not sure which answer suites you best but I've amended the code so that cell B4 will turn red if the value input to C4 is greater than 5. This only works if C4 is changed directly.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Range("A1") = Range("C4") Then
    If Target > 5 Then 'if the value of C4 is greater than 5
        Range("B4").Interior.ColorIndex = 3 'change B4 to red
    Else: Range("B4").Interior.ColorIndex = xlColorIndexNone 'else clear format
    End If
End If
End Sub

If C4 contains a formula then the code can be adapted to check for any change on the sheet that affects it's value

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("C4") > 5 Then 'if the value of C4 is greater than 5
        Range("B4").Interior.ColorIndex = 3 'change B4 to red
    Else: Range("B4").Interior.ColorIndex = xlColorIndexNone 'else clear format
    End If
End Sub

For either solution, right click on the sheet tab in question and choose view code. This will open the VB editor. At the top of (probably) the right side of the screen there will be two dropdowns. THe left of these will probably say General. Click on this and choose worksheet. You will see the sub - end sub for the selection change event. Ignore this and simply paste this code anywhere in the module (the page you are looking at) and try it out.

;-)

If a man says something and there are no women there to hear him, is he still wrong?