CODEWITHSUNDEEP
vbaexcel
user2451335
user2451335

Reputation: 91

Creating a macro to compare cell values in the same worksheet but different columns

I am trying to do up a macro that will compare the cell value in a systematic manner. I have 2 data sets. The macro that I intend to create basically will compare the value from "C3:M25" with the values from "O3:Y25".

My macro should starting comparing the values in range("C3") with range ("O3"). If the C3.value > O3.value, it will change the interior.colourindex.value and the font colour

Once it is done with the first comparison, it will move down to the next row i.e Compare range("C4").value with range("O4"). The process continues until it hits the first blank row in the column, in this case Range("C26").

Once range("C26") is an empty cell then the macro will repeat the comparison process, but this time round it will basically be comparing the value in Range("D3") with Range("P3"). The loop keeps going on until the whole process is done.

Sub ilovetocompare()

Dim ross As Long, colss As Long
Dim wb As Workbook, ws1 As Long, ws1row As Integer



Set wb = ActiveWorkbook.Sheets("Pricer")
wb.Range("C3").Activate
With ActiveCell
ws1row = Worksheets("pricer").Range("B3").End(xlDown).Rows.Count
'get the last row count


'macro will stop when it detects that the cells is filled with other colors

Do Until ActiveCell.Interior.Color = 255

'start comparing the prices
For ross = 3 To ws1row
For colss = 15 To 25 ' number of columns will remain unchanged

If ActiveCell.Value > Cells(ross, colss).Value Then

ActiveCell.Font.Bold = True
ActiveCell.Font.colour = vbWhite
'once done with comparison, jump to the next row
ActiveCell.Offset(1, 0).Activate
'the column O likewise also move 1 row down for comparison

Next ross

'when the it hits an empty row, the activecell got readjusted back to the top
ElseIf ActiveCell.Value = "" Then
ActiveCell.Offset(-ws1row, 1).Select
With Selection
Loop
'move the cell up again so that i can resume the comparsion

'create this into a loop




End Sub

Upvotes: 1

Views: 29242

Answers (1)

funk
funk

Reputation: 500

Here a suggestion:

Private Sub macrobygiada()
ColumnoneStart = 3 ' C
ColumnoneEnd = 13 'M
ColumntwoStart = 15 'O

Set wb = ActiveWorkbook.Sheets("Pricer")

TotalColumn = ColumnoneEnd - ColumnoneStart 'difference of the columnnumber C to M (3 to 13)
For Column = 1 To TotalColumn 'number of columns
    For Cell = 3 To 25 'go through the Cells
        If (Cells(Cell, ColumnoneStart).Value > Cells(Cell, ColumntwoStart).Value) Then
            wb.Cells(Cell, ColumnoneStart).Font.Bold = True
            wb.Cells(Cell, ColumnoneStart).Font.ColorIndex = 2 'colour white
        End If
    Next
ColumnoneStart = ColumnoneStart + 1
ColumntwoStart = ColumntwoStart + 1
Next
Set wb = Nothing
End Sub

Regards

Upvotes: 2

Related Questions