Excel - Macro-Highlight if difference > or < 2

June 2017




Issue


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.

Solution


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

Note


Thanks to venkat1926 for this tip on the forum.

Related


Published by aakai1056. Latest update on February 1, 2010 at 09:20 AM by aakai1056.
This document, titled "Excel - Macro-Highlight if difference > or < 2," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).