Previous Topic Next Topic

Macro for coloring cells depending on values and presenting them

Closed
Posts
11
Registration date
Monday July 17, 2017
Status
Member
Last seen
August 12, 2017
 -
Posts
2849
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 27, 2022
 -
Hello guys!!

I have an excel file with 2 columns of percentages. The first column has theoretical values of an experiment and the second column has the real values that were measured in an experiment. I want to create a macro which will be able to compare the values of the cells for every row(2 cells every row since we have 2 columns) and when the difference of the percentage is more than 5 units then I want to color the row and appear it in another pop-up or excel sheet! and this process has to be for every row. SO finally I want to have another sheet in excel which will have all the rows which had bigger difference than 5!

Can you please help me ??

1 reply

Answer 1 / 1
Posts
2849
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 27, 2022
 491
Hi PeriGr,

In the code below the source sheet is called Sheet1 and the destination sheet is called Sheet2.

Here is the code: 
Sub RunMe()
Dim mDiff As Double
mDiff = 0.05

Sheets("Sheet1").Select

For Each cell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
    If cell.Value - cell.Offset(0, 1).Value > mDiff Or cell.Offset(0, 1).Value - cell.Value > mDiff Then
        cell.EntireRow.Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        cell.EntireRow.Interior.ColorIndex = 3
    End If
Next cell
End Sub


Best regards,
Trowa
Ask a question