There are multiple steps in what I am trying to do so bear with me.

I have two sheets A and B

Sheet A

                    Jan     Feb     March   Apr Jun Jul Aug Sep Oct Nov Dec         
Names   SubNames                
Apple   Apple1      30.00                    -   -   -   -   -   -   -   -
        Apple2      45.00   10               -   -   -   -   -   -   -   -
        Apple3                               -   -   -   -   -   -   -   -
        Apple4                               -   -   -   -   -   -   -   -
        SubTotal    75.00   0.00    10.00    
Banana  Banana11                             -   -   -   -   -   -   -   -
        Banana22    15.00                    -   -   -   -   -   -   -   -
        Banana33    32.00   17.75   65.00    -   -   -   -   -   -   -   -
        SubTotal    37.00   32.75   34.50   
Cherry  1Cherry1    28.00   13.78   43.00    -   -   -   -   -   -   -   -
        2Cherry2            20.00       
        3Cherry3    20.00   16.50   100.00   -   -   -   -   -   -   -   -  
        Subtotal    48.00   50.28   143.00   -   -   -   -   -   -   -   -
Dragon  DragonF1    31.00   35.00            -   -   -   -   -   -   -   -
        Subtotal    31.00   35.00   0.00    
        Grand Total 191.00  118.03  187.50  
                            496.53          

Sheet B

                    Jan     Feb     March   Apr Jun Jul Aug Sep Oct Nov Dec     
Names   SubNames            
Apple   Apple1      30.00                    -   -   -   -   -   -   -   -
        Apple2      60.00                    -   -   -   -   -   -   -   -
        Apple3                               -   -   -   -   -   -   -   -
        Apple4                               -   -   -   -   -   -   -   -
        SubTotal    90.00   0.00    0.00
Banana  Banana11                             -   -   -   -   -   -   -   -
        Banana22                             -   -   -   -   -   -   -   -
        Banana33    37.00   17.75   34.50    -   -   -   -   -   -   -   -
        SubTotal    37.00   17.75   34.50
Cherry  1Cherry1    28.00   13.78   63.15    -   -   -   -   -   -   -   -
        2Cherry2            15.35            -   -   -   -   -   -   -   -
        3Cherry3    35.00   16.50   97.65    -   -   -   -   -   -   -   -
        Subtotal    63.00   45.63   160.80
Extra   Extra1234   30.00                    -   -   -   -   -   -   -   -
        Extra4321                            -   -   -   -   -   -   -   -
        Subtotal    30.00   0.00    0.00
Dragon  DragonF1    31.00   34.50            -   -   -   -   -   -   -   -
        Subtotal    31.00   34.50   0.00     -   -   -   -   -   -   -   -
     Grand Total    251.00  97.88   195.30
                            544.18      

There are columns from A - O

I need to check if the values in Sheet B Column B match with values in Sheet A and Column B

If they do, I need the values under Jan, Feb and March to be compared as follows:

Change color of cell to Green in Sheet B if the value increases from Sheet A to B Change color of cell to Decreases in Sheet B if the value decreases from Sheet A to B

Subtotals and Totals fields can be ignored.

I keep getting errors, but I am not sure if my code is complete.

Public Sub Edit()
    Dim myRange As Range
    Dim iCell As Range
    Dim LastRow As Integer, Row As Integer, Col As Integer
    Dim CSheet As Integer, PSheet As Integer
    CSheet = ActiveSheet.Index
    PSheet = CSheet - 1

    Set cs = ActiveSheet
    Set ps = Worksheets(PSheet)

    'selecting range of active region
    Set myRange = Range(ActiveSheet.Range("A3:O3"), ActiveSheet.Range("A3:O3").End(xlDown))

    LastRow = Cells(Rows.Count, 15).End(xlUp).Row

    'Highlighting all rows with no entries for forecast
    For Row = 3 To LastRow
        If WorksheetFunction.CountBlank(Range("C" & Row & ":O" & Row)) = "13" Then
            Range("C" & Row & ":O" & Row).Style = "Note"
        End If

        'Highlighting green and red
        If cs.VLookup(Range("B" & Row).Value, ps.Range("B2:B100"), 0) Then
            For Col = 3 To 15
                If cs.Cells(Row, Col) >= ps.Cells(Row & Col) Then
                    cs.Cells(Row, Col).Style = "Good"
                Else
                    cs.Cells(Row, Col).Style = "Bad"
                End If
            Next Col

        End If

    Next Row
End Sub

1 Answers

0
Dominique On

If I read your question right, you would like to modify the formatting of a cell, based on some information, which can be retrieved from a formula.

There are two different cases:

  1. You want the formatting of that cell only to happen, based on user interaction (like clicking a button). In that case, it's ok to opt for VBA.
  2. You want the formatting of that cell to happen automatically. In that case, it's better to opt for conditional formatting.

In case you opt for conditional formatting, you might need a formula, based on the following:

=VLookup(A!B3;B!$B$2:$B$100;2) // this will give the value for January for Apple1 in sheet B.
                               // In case "Apple1" is not there, you get a #N/A error.
=ISNA(...)                     // You might use this for catching #N/A errors.
...

So, basically, you'll end up with a formula like this:

=ISNA(VLOOKUP(...);0) - ISNA(VLOOKUP(...);0)>0