Formula & Macro

[nik_gujarathi]

Hello friends
I have a formula in each cell of column L and R.
Example
Formula in cell L6 is =IF(J6<>K6,0,1) & Formula in cell R6 is = IF(P6<>Q6,0,1)
I was looking for a macro that will give a error message "Warning" if the value of cells in column L and R is 1

[Kevin B]

I select the cells in each of the columns (L and R) and name them CheckValues.

The following macro assumes that the sheet in use is Sheet1, change this to
whatever is appropriate to your environment.

Open the VBE (Alt + F11) and in a new module enter the following code:

Sub CheckCells()

Dim rng As Range
Dim wb As Workbook
Dim ws As Worksheet
Dim i As Integer

Set wb = ActiveWorkbook
Set ws = wb.Worksheets("Sheet1")
Set rng = Range("CheckValues")

For i = 1 To rng.Cells.Count
If rng.Cells(i) = 1 Then
MsgBox "A cell in either R or L has evaluated to a 1."
Exit For
End If
Next i

Set rng = Nothing
Set wb = Nothing
Set ws = Nothing

End Sub

Open the worksheet module for whatever sheet the ranges are on and enter the
following in the Worksheet's Change event:

Private Sub Worksheet_Change(ByVal Target As Range)

CheckCells

End Sub

--
Kevin Backmann


"nik_gujarathi" wrote:

>
> Hello friends
> I have a formula in each cell of column L and R.
> Example
> Formula in cell L6 is =IF(J6<>K6,0,1) & Formula in cell R6 is =
> IF(P6<>Q6,0,1)
> I was looking for a macro that will give a error message "Warning" if
> the value of cells in column L and R is 1
>
>
> --
> nik_gujarathi
> ------------------------------------------------------------------------
> nik_gujarathi's Profile: http://www.excelforum.com/member.php...o&userid=34522
> View this thread: http://www.excelforum.com/showthread...hreadid=546036
>
>