Can I have excel vba check to see if all the values in a range are equal?

Question

Hi there! I'm trying to write a macro to see if all the cells in a range are the same value. I would have no problem just going through and either checking them individually or each against each other (like if B3=B4=B5 kind of thing) but my issue is the range is based off of a variable (and this range will continue to move down a large column of data, offset by one cell at a time, and continue to check to see if all the values are the same). Even though I don't think the variable will be changing, I want to make sure this is user friendly just in case a bigger range needs to be checked. Here is kind of what I'm thinking so far, where the variable "RangeCheck" is what might be changing in the future.


For Start=1 to Rows
Range("B4:B" & RangeCheck).Select

and here is where I get lost! The whole variable thing is just really throwing me off.



Thanks so much in advance!!!!!

Answer 1

Here is a macro that will validate the range B4 to B and the last populated row in column B. If an inconsistent value is found in the range, a message box will return the cell reference. If all values are consistent, a message box will confirm.





Sub RangeCheck()

Dim i, LastRow

LastRow = Range("B" & Rows.Count).End(xlUp).Row

For i = 4 To LastRow - 1

If Cells(i, "B").Value <> Cells(i + 1, "B").Value Then

MsgBox "The value in cell B" & i + 1 & " is inconsistent!", _

vbCritical, "Data varies"

Exit Sub

End If

Next

MsgBox "The values in B4:B" & LastRow & " are consistent.", _

vbInformation, "Data is consistent"

End Sub



I infer that you are capable of copying the macro to a standard module and attaching it to a command button, or a keyboard shortcut.

Answer 2

1

Answer 3

This code verify any resolution not most effective a1:a4 and investigate if any worth is the same as scan. If it finds a worth no longer equal to scan, suggests a message box say that and shut code. Sub trying out() dim xcel as cells for each xcell in resolution.Cells if xcell.Price <> "experiment" then msgbox "no longer all cells are equal to test",vbinformation exit sub end if next xcell msgbox "All chosen cells are equal to scan",vbinformation finish if