Conditional Formatting based on another Cell

[pfitz]

Hi All, I'm having a little trouble with conditional formatting and any help would be appreciated.

I currently have 4 columns of data (columns A, B, C, D). These 4 columns are followed by 4 more columns that relate to the first four (E, F, G, H). For example: column A relates to column E, column B relates to column F, column C relates to column G and finally column D relates to H. So Cell A6 relates to the cell four cells to the right, E6. If E6 has value =1 then A6 gets colored blue. I need to do this for all four columns (A-D).

I am currently putting conditional formatting in phpexcel so that would be the best way, although I am running a macro after the data is in excel so I can also make this condition in VBA macro. Thanks,

Parker

 

[RickXL]

I can't help with phpexcel but if you use the macxro recorder in Excel it gives you the following code:

Sub Macro1()
    Cells.FormatConditions.Delete
    Columns("A:D").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=E1"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = 0
        .Color = 15773696
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

If E1 is TRUE then A1 is blue.
If E1 is FALSE then A1 is not filled.
0 and 1 can be used instead of TRUE and FALSE.


This should work too:

Sub Macro1()
    Cells.FormatConditions.Delete
    With Range("A:D")
        .FormatConditions.Add Type:=xlExpression, Formula1:="=E1"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 15773696
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
    End With
End Sub

I hope this helps.

 

[pfitz]

Rick thanks a lot for the reply it helped solve my problem. Although, it seems like only column A is affected by column E. Is there any way to also format column B in relation to F, C in relation to G, D in relation to H.

For now, I am just duplicating this code 4 times and changing the range. Not sure this is the most efficient way to do this. Although it does work so thank you!

 

[RickXL]

When I tried it, E affected A, F affected B etc.

Is that not happening for you or have I misunderstood the requirement?

Regards,

 

[pfitz]

My Mistake Rick, It is working like a charm... thanks again

 

[RickXL]

No problem - thanks for the feedback.