Compare two ranges using the fill color changes

[Remphan]

1. Range(E6:G) and filled to the last row in any color (color shading not interrupted) - basic control data
2. Range(L6:N) and filled to the last row in any color (color shading not interrupted) - new values
3. There is a continuous column D - on the basis of it is filled with the color of the entire table. That is, we can focus on a single column, the last line defining two ranges, or a condition - that the two bands, old and new - have a continuous fill color to the last row.
4. If the range of point 2 is unlike matching point range cells point 1 - is to allocate it any color

Help pls with macro (not with conditional formatting). I can do that with smart table and conditional formatting, but needs macro.
Visual example - what i need - is in attach.

[Keebellah]

I don't understand your explanation but a macro to change the fill color is very simple:

Please Login or Register  to view this content.

You can also use RGB

Please Login or Register  to view this content.

You can apply it to a Range as well

Please Login or Register  to view this content.

Hope this helps to put you on the right track

BTW: its fun to see Book1 in Cyrrillic Kniga1

[Remphan]

Look the 2nd example pls. I do what i need by conditional formatting and smart table.
That formulas =($D6<>"")*(L6<>E6) for =$L$6:$N$31

But need macro instead of this example (need to avoid conditional formatting and smart table).
Need compare Range (E6:G) till last row with Range(L6:N) till last row. And if cells in 2nd range <> cells values in first range - then fill them any color.

[Keebellah]

I don't quite see what your conditional formatting does, but I did add a macro and removed the conditional
[cdoe]
Public Sub CheckRange()
Dim cell As Range
Dim xRow As Long
Dim lstRow As Long
For Each cell In Range("L6:N31")
cell.Interior.Color = RGB(146, 208, 80)
If Cells(cell.Row, "D") <> "" And (Cells(cell.Row, "L") <> Cells(cell.Row, "E")) Then
cell.Interior.Color = RGB(255, 102, 204)
End If
Next cell
End Sub
[/code]

The macro will not run automatically but that you can do yourself with a worksheet event

[Remphan]

Fine, but works for one pair columns and for fixed range only. And fill whole row, not only one cell.

But need compare columns: L with E, M with F, N with G.
And in case cell value in column L<> the same cell adress in column E then fill by color only one cell in L
Next for M and N columns.

And need it to for dynamic columns like from L6 to last row in L
Last rowfound like that Range("L6" & Rows.Count).End(3).Row

But no enough knowledges to expand your macro on my case Excuse my Eng pls..

[Keebellah]

No problme, English is fine, my Russian is worse
Then I think it is time you start to learn
You have the macro so try and practice.
It is bedtime here. I'll see if I can do more tomorrow.
The macro works for all rows between 6 and 31. Will there be more rows?

[Remphan]

Yes, the number of rows is a very dynamic value. from 6 row till last filled. Thank you.

[Keebellah]

This works now and if the table is expanded it will still work

[Remphan]

Your macro works well

I found more little macro, but its not work automatically for L,M,N columns (on every chanlls. Only, when manyally enter values...
Can anybody correct it?

Please Login or Register  to view this content.

[Keebellah]

Check my way of doing it.