Conditional Formating based on mutiple columns

All Replies

• Wednesday, May 02, 2012 4:28 PM

   

   
  

  0
  What part of this are you having trouble with?
  • Reply
  • Quote

   
• Wednesday, May 02, 2012 5:00 PM

   

   
  

  0

  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 Alsallam Wednesday, May 02, 2012 5:03 PM
  •  
  • Reply
  • Quote

   
• Thursday, May 03, 2012 9:46 AM

  Moderator

   

   
  

  1

  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 Alsallam Thursday, May 03, 2012 4:20 PM
  •  
  • Reply
  • Quote

   
• Thursday, May 03, 2012 4:20 PM

   

   
  

  0
  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

  • Reply
  • Quote