Results 1 to 4 of 4
  1. #1
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    Changing Destination worksheet cell background color

    I am trying to change the destination cell on a worksheet a color if it meets a criteria. The below code is not all the code. Where would I put this in order for it to show up correctly? I understand it would have to be after the For iRow or I get an error 9. I have tried different variations where the cells in Column 16 turn black, or the first cells are blank, but not where the rows where "Plat" are present.



    Thanks

    Code:
        Dim R As Long
        Dim G As Long
        Dim B As Long
        Dim iRow As Long
        Dim RGBCode As Long
        R = 255
        G = 0
        B = 0
        strRangeToCheck = "A1:AM30000"
        strVar = "RQ"
        strRowType = "Plat"
    
    If varSheetA(iRow, 27) = strVal Then
      wkSheetC.Cells(iRow, 16).Interior.Color = RGBCode
    End If
    
        For iRow = 2 To 250
           For iCol = 1 To 29
                 If varSheetA(iRow, iCol) <> varSheetB(iRow, iCol) Then
                  If varSheetA(iRow, 8) = strRowType Or varSheetB(iRow, 8) = strRowType Then
                       wkSheetC.Cells(iRow, 2) = varSheetB(iRow, 2)
                        wkSheetC.Cells(iRow, 8) = varSheetB(iRow, 8)
                         wkSheetC.Cells(iRow, 14) = varSheetB(iRow, 14)
                          wkSheetC.Cells(iRow, 15) = varSheetB(iRow, 15)
                           wkSheetC.Cells(iRow, 16) = varSheetB(iRow, 16)
                             wkSheetC.Cells(iRow, iCol) = varSheetA(iRow, iCol) & "  ===>  " & varSheetB(iRow, iCol)
                  bFilesIdentical = False
                 End If
                End If
               Next iCol
             Next iRow

  2. #2
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    I figured it out:
    Code:
     For iRow = 2 To 100
            For iCol = 1 To 16
                 If varSheetA(iRow, iCol) <> varSheetB(iRow, iCol) Then
                       wkSheetC.Cells(iRow, 2) = varSheetB(iRow, 2)
                        wkSheetC.Cells(iRow, 8) = varSheetB(iRow, 8)
                         wkSheetC.Cells(iRow, 14) = varSheetB(iRow, 14)
                          wkSheetC.Cells(iRow, 15) = varSheetB(iRow, 15)
                        If varSheetA(iRow, 36) = strVar Then
                           wkSheetC.Cells(iRow, 16).Interior.ColorIndex = 22
                            wkSheetC.Cells(iRow, 16).Font.ColorIndex = 1
                             wkSheetC.Cells(iRow, 16).Font.Bold = True
                              wkSheetC.Cells(iRow, 16) = varSheetB(iRow, 16)
                            Else
                              wkSheetC.Cells(iRow, 16) = varSheetB(iRow, 16)
                        End If
                   wkSheetC.Cells(iRow, iCol) = varSheetA(iRow, iCol) & "  ===>  " & varSheetB(iRow, iCol)
                  bFilesIdentical = False
                 End If
               Next iCol
             Next iRow
    I don't need the RGB code

  3. #3
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    That's a little trickier than it looks, I think. Do you want to do that colour change regardless of whether varSheetA(iRow, iCol) <> varSheetB(iRow, iCol), or only if it is? That will dictate where you put the code and/or how you code it.

    Regardless, the code isn't going to work as it is anyway, because you don't define what strVal is. Did you mean to use strVar in that If statement?

  4. #4
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    I didn't put up all the code DIM & Set for space sake. It works just fine looking at Sheet B. I can put an OR and add Sheet A for either one = to strVal.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 08-26-2013, 01:26 PM
  2. Replies: 5
    Last Post: 06-28-2013, 06:11 PM
  3. Replies: 3
    Last Post: 04-28-2013, 04:10 PM
  4. Replies: 1
    Last Post: 01-18-2013, 12:32 AM
  5. Background Color
    By Meccer in forum Access
    Replies: 4
    Last Post: 05-30-2011, 06:49 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums