Previous Topic Next Topic

Macro for coloring cells depending on values and presenting them

Closed
PeriGr Posts 11 Registration date Monday July 17, 2017 Status Member Last seen August 12, 2017 - Jul 17, 2017 at 08:22 AM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - Jul 18, 2017 at 12:09 PM
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
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Jul 18, 2017 at 12:09 PM
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
0