Previous Topic Next Topic

Excel Macro-Highlight if difference > or < 2

Solved/Closed
ksaw - Jan 30, 2010 at 01:55 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jan 30, 2010 at 09:20 PM
Hello,

I need help writing an Accounting excel macro I am working on. It appears that everything is working fine, except for one part I need some help with.

I have 2 worksheets formatted identically, however, some of the information changes from worksheet 1 to worksheet 2. I want to set up a macro to do a check on columns "J", "M", "P", "S", "V", and "Y" for each cell. here is an example what I would need:

-If (Worksheet1!M24-Worksheet2!M24) is greater than 20 or less than -20, change cell background Worksheet1!M24 lavender
-I need the formula to be done as a check, and not overwrite the data in either worksheet

This means that if M24 in worksheet 1 is 200.00, and M24 worksheet 2 is 275.00, then M24 in worksheet 1 will still show as 200.00 but it will now be highlighted lavender.

Any ideas how I can do this?? I am building the macros in VBA, which I have very little experience with.
Related:

1 response

Answer 1 / 1
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jan 30, 2010 at 09:20 PM
your choice of columns J, M, N etc that its col j+3 is M and col M+3 is P etc it is ok
your actual data starts from row 2 , row 1 being column headings
There is no gap in the data at least in column J

but you have writeen that if difference between the cells of the two sheets eiteshr >20 or less than 20 it should be colored. That means only if it is 20 it is not colored. IS THIS LOGIC OK . please check again

on these assumption the macro is given below. test it and post back comments.
If there is a bug or error the error should be clearly elucidated. If the macro stops the line where it stops and error message.

the second macro undo removes the coloring;. I have used the color numbe as r 3 for red. If you insist on lavender you have to find out the number of the color, because I may misunderstand what you mean by lavender. in an empty cell
color the cell in lavender. suppose the cell is E13 then go to vb editor immediate window and type
?range("e13").interior.colorindex
and keep the cursor at the end of this line and hit enter key and you get the color number. use that in the macro

the two macros are 

Sub test()
Dim col1 As Integer, col2 As Integer, col As Integer, rrow As Integer
Dim lastrow As Integer
col1 = Range("J1").Column
col2 = Range("Y1").Column
lastrow = Worksheets("sheet1").Range("J2").End(xlDown).Row
'MsgBox lastrow
Worksheets("sheet1").Cells.Interior.ColorIndex = xlNone
For col = col1 To col2 Step 3
For rrow = 2 To lastrow

'MsgBox Cells(1, col).Address
'MsgBox rrow
If Worksheets("sheet1").Cells(rrow, col) - Worksheets("sheet2").Cells(rrow, col) > 20 Or _
    Worksheets("sheet2").Cells(rrow, col) - Worksheets("sheet2").Cells(rrow, col) < 20 Then
    Worksheets("sheet1").Cells(rrow, col).Interior.ColorIndex = 3
    End If
Next
Next
End Sub



Sub undo()
Worksheets("sheet1").Cells.Interior.ColorIndex = xlNone
End Sub
1