Conditional formatting in VBA, cell reference problem

doq85

New Member
Joined
Jul 20, 2019
Messages
5
I'm trying to highlight differences between "Yhteenveto" and "Titania" sheets.

I have a VBA code that compares two data sets in two different sheets "TYÖVUOROT" and "TITANIA"
In "TITANIA" sheet i have the original data.
In "TYÖVUOROT" sheet i have a copy of original data from "TITANIA" sheet and in this sheet i will make changes to the data.

I have a VBA code that compares "TYÖVUOROT" and "TITANIA" sheets, then copies rows that are not equal to "YHTEENVETO" sheet.

Now i'm trying to highlight the cells that has been changed.
In this example the copied data starts from cell A23 and everything works fine.

Problem is that i have two sets of gathered data in this "YHTEENVETO" -sheet.
First set starts from A15 and might be anything from 0 to xxxx rows.
After this starts the dataset that i have problem with.
I want to highlight what cells have changed between this sheet and "TITANIA"-sheet.

In this example the first copied data starts from A23 and everything works.

"Picture from "Yhteenveto"-sheet"
1667557002924.png



I have highlighted the cell reference to the code below, that i think the problem is.
This cell is always going to be the first cell below "Rivinumero" in column A. I have tried to use match and address functions but can't get it to work.
Do i need to change the approach to this problem or is there a way to fix this?

Sub test()
Dim findrow As Range
Dim vertaa As FormatCondition

lr = Cells(Rows.Count, "A").End(xlUp).Row
With Worksheets("YHTEENVETO")
Set findrow = .Range("A:A").Find(What:="Rivinumero", LookIn:=xlValues).Offset(1, 3)
Range(findrow, "X" & lr).Select
Set vertaa = Selection.FormatConditions.Add(xlCellValue, xlNotEqual, "=XLOOKUP($A23;TITANIA!$A$6:$A$1000;TITANIA!D$6:D$1000)")

End With
With vertaa
.Interior.Color = RGB(230, 9, 145)
.Font.Color = RGB(255, 255, 255)
.Font.Bold = True
End With
With vertaa.Borders
.LineStyle = xlContinuous
.ThemeColor = 1
.TintAndShade = -0.24994659260842
.Weight = xlThin
End With
End Sub

If the first dataset gets more rows and cell reference changes, highlighting goes nuts
1667557312083.png
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top