Conditional Formating based on mutiple columns

# Conditional Formating based on mutiple columns

• Wednesday, May 02, 2012 2:46 PM

I have table called table1. I want conditional formatting to color rows in table1 based on value in 4 columns as follows:

If column "Amount Change" has a cell that = 0 and the other 3 columns have 1 in each cell for the same row then color the row "yellow".

If column "Company Code" has a cell that = 0 and the other 3 columns have 1 in each cell for the same row then color the row "pink".

If column "Tax Code" has a cell that = 0 and the other 3 columns have 1 in each cell for the same row then color the row "blue".

If column "Missing Payment" has a cell that = 0 and the other 3 columns have 1 in each cell for the same row then color the row "green".

If more than one column has 0 in a row then color should be "Red".

can someone help?
thanks

Al

Al Sallam

### All Replies

• Wednesday, May 02, 2012 4:28 PM

What part of this are you having trouble with?
• Wednesday, May 02, 2012 5:00 PM

Coming up with a formula to check for these scenarios. I created another column (Variance) and want to have a formula in it to check which of the 4 columns has zeros and which have ones. Then I would use the conditional formating based on this one column to determine the row color, here is to illustrate:

Scenario  Amount      Company      Tax       Missing           Variance

#             Change      code              code    Payment

1                  0               0                  0              0            Combination

2                  1               1                  0              1            TaxCode

3                  0               1                  1              1             AmountChange

4                  1               0                  1              1             CompanyCode

5                  1               1                  1              0             MissingPayment

6                  1               0                  1              0               Combination

7                  0               0                  1              1                Combination

If there is one column has 0 and the rest has ones, then the formula in the Variance column will return the name of the column that has the zero in it. If more than one column have zeros then the formula will return the word "Combination". I have tried using IF & IIF functions but not able to get them to work for me.

conditional formating would use the variance column to determine the color of the row as follows:

scenarios 1,6,and 7...row will be Red
scenario 2, row will be blue
scenario 3, row will be yellow
scenario 4, row will be pink
scenario 5 row will be green

Of course if there is a better way I would be glad to use it. This is just what I thought of, to resolve this issue.
thanks
Al

Al Sallam

• Edited by Wednesday, May 02, 2012 5:03 PM
•
• Thursday, May 03, 2012 9:46 AM
Moderator

Hi,

Here’s my code for you to judge the value of column Variance:

Public Sub variance()

Dim row_start As Integer

Dim row_end As Integer

Dim i_row As Integer

Dim j_column As Integer

Dim col_start As Integer

Dim col_end As Integer

Dim zero_num As Integer

Dim zero_column As String

row_start = 2

row_end = 8

col_start = 2

col_end = 5

For i_row = row_start To row_end

zero_num = 0

For j_column = col_start To col_end

If Cells(i_row, j_column) = 0 Then

zero_num = zero_num + 1

zero_column = Cells(1, j_column).Value

End If

Next

If zero_num > 1 Then

Cells(i_row, 6) = "Combination"

Else

Cells(i_row, 6) = zero_column

End If

Next

End Sub

Note: according to you table, change the value of row_start ,row_end ,col_start ,col_end  and Variance column number in the code.

Then we can according to the Variance column to set the color to each record.

Jaynet Zhang

TechNet Community Support

• Marked As Answer by Thursday, May 03, 2012 4:20 PM
•
• Thursday, May 03, 2012 4:20 PM

Jaynet,
Thank you very much, this was very helpful. I made a little change to the code to account for scenario where all columns were 1, the code was flagging it as (missing Payment) since this was the last value for "zero_column", as follows:

Public Sub variance()

Dim row_start As Integer

Dim row_end As Integer

Dim i_row As Integer

Dim j_column As Integer

Dim col_start As Integer

Dim col_end As Integer

Dim zero_num As Integer

Dim zero_column As String

row_start = 7

row_end = 17

col_start = 20

col_end = 23

For i_row = row_start To row_end

zero_num = 0

For j_column = col_start To col_end

If Cells(i_row, j_column) = 0 Then

zero_num = zero_num + 1

zero_column = Cells(6, j_column).Value

End If

Next

If zero_num > 1 And zero_num < 4 Then

Cells(i_row, 24) = "Combination"

Else

Cells(i_row, 24) = zero_column

End If
If WorksheetFunction.Sum(Cells(i_row, 20), Cells(i_row, 21), Cells(i_row, 22), Cells(i_row, 23)) = 4 Then
Cells(i_row, 24) = ""
End If

Next

End Sub

Al Sallam