CODEWITHSUNDEEP
vbaexcelmacrosconditional-formatting
sidharth.1989
sidharth.1989

Reputation: 15

Excel Conditional Formatting

I have a query regarding macros in excel based on the following requirement

Things that I am trying to achieve:

1:Sheet 1 will contain my data input(Example)

A   B   C   D   E
1   2   3   4   5
    3   5   5

As seen sheet 1 contains 5 columns and 3 rows(row1 contains the column names,row2 and row3 contain the data values).

2:I need to check if the column names present in sheet 1 with the values present in sheet 2(sheet 2 contains the below values in column 1)

A
E

3:First Check if any values of row1 of sheet1(Column Names in my example they are A,B,C,D,E) match with any of the column names present in sheet2(In my case it is A,E)

4:If a match is found ,check the following (in my case columns A and E found).

5:In that case I would want to flag the values corresponding to these columns,that is if they contain any value they should be flagged(This can be either highlighted or moved into a different sheet).Only Null values are permitted

6:So in my example row2 should be either moved into a different sheet or the values under A and E should be highlighted or the entire row moved into sheet 3

I am more of an ETL person and an excel rookie but trying to automate it through macros.Looking for an approach on how to implement this

Approach That I have tried

1:Paste the values of the column name from sheet1 into a new sheet3 and pasted as transpose

2:compare these values with sheet2 values using vlookup

3: Whenever a match is found use the equal operation to compare the cell values and generate a boolean return value

Well after this before I proceed I want to check if my approach is correct at all!! Appreciate your inputs guys

P.S:I am not looking for someone to write the entire code but a generic way of handling the above scenario in VBE

Upvotes: 1

Views: 117

Answers (2)

Mrig
Mrig

Reputation: 11712

Try this:

Sub Demo()
    Dim lastRow As Long, colNum As Long, i As Long
    Dim inputRng As Range, checkRng As Range, rngFound As Range
    Dim inputWS As Worksheet, checkWS As Worksheet

    'assigning worksheets to variable        
    Set inputWS = ThisWorkbook.Sheets("Sheet1")
    Set checkWS = ThisWorkbook.Sheets("Sheet2")

    'setting the range of both worksheets
    Set inputRng = inputWS.Range("A1").CurrentRegion
    Set checkRng = checkWS.Range("A1:A" & checkWS.Cells(Rows.Count, "A").End(xlUp).Row)

    For Each cel In checkRng
        'find Sheet2 values in Row1 of Sheet1
        Set rngFound = inputWS.Rows(1).Find(cel.Value)
        If Not rngFound Is Nothing Then
            'get the column number if value found
            colNum = rngFound.Column
            lastRow = inputWS.Cells(Rows.Count, colNum).End(xlUp).Row
            'loop through the entire column to find non-empty cells
            For i = 2 To lastRow
                If Not IsEmpty(inputWS.Cells(i, colNum)) Then
                    inputWS.Cells(i, colNum).Interior.Color = vbRed
                End If
            Next i
        End If
    Next
End Sub

See image for reference:

enter image description here

Upvotes: 1

user6432984
user6432984

Reputation:

You never really say what you want to do with the data. This code will highlight the invalid cells.

Sub HighlightInvalidData()
    Dim rData As Range, rCheck As Range, c As Range, target As Range
    Dim i As Long
    Set rData = Sheet1.Range("A1").CurrentRegion
    Set rCheck = Range(Sheet2.Range("A1"), Sheet2.Range("A" & Rows.Count).End(xlUp))

    For i = rData.Rows.Count To 2 Step -1

        For Each c In rCheck
            Set target = rData.Rows(i).Columns(c.Text)
            If Not IsEmpty(target) Then
                'Highlight Invalid Data
                target.Interior.Color = vbRed
            End If
        Next

    Next

End Sub

You can hightlight the invalid rows instead using

'Highlight Invalid Row
Sheet1.Rows(target.Row).Interior.Color = vbRed

I iterated from the last row to the first row. In this way, you can delete the rows if necessary.

'Delete Row
target.EntireRow.Delete

Upvotes: 0

Related Questions