Color cell dependant on other cell value

[lynnnow]

Hi,

I've got a few columns that have only two values that can go in them i.e. 24 and 4. When this column is fed with 4, two columns left should be filled with the color yellow.

This can be achieved with the ThisWorkbook Range option, however, I've never tried it before and am lost on how to go about it.

I need help in writing this code.

The columns are simple:

File No. Office TAT Time In Time Out

The TAT column gets the 4/24 values. When the value 4 is keyed in a particular row, the respective File No. row should be changed to a yellow interior.

I know this can be done, but don't know how to do it.

Your help is appreciated.

Thanks

Lynnnow

[Zack Barresse]

Hi,

I'm wondering why standard Conditional Formatting can't be utilized here? I know, I know, we're a VBA site, but when a more efficient solution is viable .. why not?

Can you upload a sample workbook?

[lynnnow]

Hi Firefytr,

I know that Conditional formatting will be a good option, but the problem is that the input cell is different from the one to be formatted. I tried that option prior to my posting and since I don't have experience with the OnEvent trigger function on an excel sheet, I have asked for your help. Attached is the file for any clues you might find for helping me out.

The shortcut for the macro that i've written is Ctrl+L. It brings up an inputbox and the file number is keyed in there. This file number is then saved in a central excel file and put in the current file.

Hope you can solve my predicament.

Thanks.

Lynnnow

[Zack Barresse]

Well, the good thing about Conditional Formatting is that you don't have to reference the cell you are in to make use of CF. You can reference any cell from anywhere and still be a valid condition.

These are the steps I took to use standard Conditional Formatting (recommended):

• Select column A (ensuring A1 is active)
• Select Format (menu) --> Conditional Formatting
• Select Formula Is..
• Enter this formula ...
• =(ROW()<>1)*(C1=4)
• Select your Yellow highlight Pattern.
• Press Ok.

As far as any VBA goes, I didn't see any routine in your workbook. But you could use a (worksheet) change event like this if desired ...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub 'multiple cells
    If Target.Row < 2 Then Exit Sub 'headers
    If Target.Column = 3 Then 'correct column
        If Target.Value = 4 Then
            Cells(Target.Row, 1).Interior.ColorIndex = 6
'        ElseIf Target.Value = 24 Then
'            Cells(Target.Row, 1).Interior.ColorIndex = 3
        Else
            Cells(Target.Row, 1).Interior.ColorIndex = 0
        End If
    End If
End Sub

Note that there are 2 lines in the code above that I have commented out. This can be uncommented if you wish to add more than one condition on it, say if you wanted a different action for a 24 value. You can do this with Conditional Formatting (still recommended) but only up to 3 - possibly up to 3 more using Custom Formatting, so up to 7 formats before needing VBA.


HTH

[lynnnow]

Hi Zack,

Thanks for the code. I'll keep in my safebox for later use, since the CF option is lighter and easier to use. It is gr8.

Thanks again,

Lynnnow