VBA Formula to Check if the Range between blank rows are the same and loop

[VBA learner ITG]

Hi all,

I need your advice if possible.

I have a dataset which i am trying to determine between blank rows with variable blocks of data ranges in Column R to see if the numerical values are the same within the range and if the value are the same I want "True" and where its not exact "False" value all the way down in the range.

I have tried with the below code and I cant get past the below code as I get a : Run-time error '1004': Application or object-defined error

.Formula = "=AND(EXACT(" & Rng.Offset(, 1).Address(0, 0) & ")"

This is the code I am using.

Sub CHECK_RANGE()
Dim Rng As Range

For Each Rng In Range("R2", Range("R" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
With Rng.Offset(Rng.Count, 1).Resize(1, 7)
.Formula = "=AND(EXACT(" & Rng.Offset(, 1).Address(0, 0) & ")"
.Value = .Value
End With
Next Rng

End Sub

 

[VBA learner ITG]

Apologies I forgot to attach a screen shot of my data set so you can see what I am doing in column R and trying to achieve in column V.

 

[VBA learner ITG]

Sub LoopArea()

Dim lr As Long

Dim area As Range

Dim sh As Worksheet

Set sh = ActiveSheet

lr = sh.Cells(Rows.Count, "R").End(xlUp).Row

'Constant based Area in Col R

For Each area In sh.Range("R2:R" & lr).SpecialCells(xlCellTypeConstants, 23).Areas

area.Interior.Color = vbGreen

area(1,3).value = WorksheetFunction.Countif(area,area(1,1)) <> area.cells.count

Next area

End Sub

 

[Peter_SSs]

@VBA learner ITG
When posting vba code in the forum, please use the available code tags, not quote tags.
My signature block below has more details. I have changed the tags for you this time in post 3.