CODEWITHSUNDEEP
vbaexcel
Iohann Oro
Iohann Oro

Reputation: 71

Cells with different background in VBA

I have a excel file integrated with my SQL database. When opened, the excel file get the data from the database and paste into my excel file. I've wrote this simple code which changes the cell color whenever one cell change its value:

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 6
End Sub

Now I would like to create a button to update the data into my database, but only the cells that have changed their values.

Is there any way to find cells with different background? If no, is there other ways to track cells which changed their values?

Upvotes: 0

Views: 84

Answers (2)

0m3r
0m3r

Reputation: 12499

Here is an example to find cells with one color

Edit as you need it

'Select the color by name
'vbBlack, vbBlue, vbGreen, vbCyan,
'vbRed, vbMagenta, vbYellow, vbWhite
'or if you prefer, you can use the RGB function
'to specify a color
'Colr = RGB(0, 112, 192)

Example search for Yellow cells

Sub Test()
    Dim Cel As Range
    Dim Colr As Long
    Dim Colred As Range

    Colr = vbYellow

    Set Colred = Nothing
    '// select cells to search or set range
    For Each Cel In Selection  
        If Cel.Interior.Color = Colr Then
            If Colred Is Nothing Then
                Set Colred = Cel
            Else
                Set Colred = Union(Colred, Cel)
            End If
        End If
    Next
    If Colred Is Nothing Then
        MsgBox "No cells match that color"
    Else
        Colred.Select
        MsgBox "Selected cells match the color:" & _
            vbCrLf & Colred.Address
    End If
    Set Cel = Nothing
    Set Colred = Nothing
End Sub

Upvotes: 0

Nishank
Nishank

Reputation: 475

first find the range of data using xldown and xltoright

Then run a for loop to check each and every cell

Inside the forloop use if condition to check the cell color

If the cell color condition satisfies, then run the action u want else endif and go to the next cells using for loop.

Upvotes: 1

Related Questions