Dear Mr.Roy,
Thanks for your reply.
My actual need is the popup to be display display only one time when it is crossed my target set value
Dear Mr.Roy,
Thanks for your reply.
My actual need is the popup to be display display only one time when it is crossed my target set value
Ok. Thanks for your valuable reply.
Dear all
I am having the following code for popup message.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim a As Range, rng As Range Dim counter As Long Set rng = Sheets("Data").Range("H30:H136") For Each a In rng If a >= 3 Then counter = counter + 1 a.Offset(0, -5).Interior.ColorIndex = 44 MsgBox "Aggregate value is crossed the tolerance limit." & vbNewLine & "Please check the value", vbInformation, "SSIPL Warning" ElseIf a <= -3 Then counter = counter + 1 a.Offset(0, -5).Interior.ColorIndex = 44 MsgBox "Aggregate value is crossed the tolerance limit." & vbNewLine & "Please check the value", vbInformation, "SSIPL Warning" ElseIf a <= 3 And a >= -3 Then counter = counter + 1 a.Offset(0, -5).Interior.ColorIndex = 0 End If Next a End sub
with this code the popup is displaying every time when i select any cells. But i want to display the message only I select the particular cell or row where the value is crossed the set limit.
Can any one please explain me how to do this
Hi carim,
It thanks for your reply.
I had changed your code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim a As Range, rng As Range
Dim counter As Long
Set rng = Sheets("Data").Range("H30:H136")
For Each a In rng
If a >= 3 Then
counter = counter + 1
a.Offset(0, -5).Interior.ColorIndex = 44
MsgBox "Aggregate value is crossed the tolerance limit." & vbNewLine & "Please check the value", vbInformation, "SSIPL Warning"
ElseIf a <= -3 Then
counter = counter + 1
a.Offset(0, -5).Interior.ColorIndex = 44
MsgBox "Aggregate value is crossed the tolerance limit." & vbNewLine & "Please check the value", vbInformation, "SSIPL Warning"
ElseIf a <= 3 And a >= -3 Then
counter = counter + 1
a.Offset(0, -5).Interior.ColorIndex = 0
End If
Next a
End sub
Display More
This is working fine. But the message is popup again and again if i select any cell.
Is it possible the message is to be popup once i select the particular cell where the value is crossed the set limit
Hi i have one more small clarification.
With Range("C30:C187")
.Formula = _
"=IF(H30>=2,C30.Interior.color = vbYellow,IF(AND(A30="""",B30=""""),"""",B30-A30))"
.Value = .Value
End With
In range H30:H187 any value >=2 then the range C color to be change to yellow
Example: H35 value is >=2 then C35 color to be change to yellow
Can you please clear this
Dear Carim,
Your code is working great. Thanks for your support
Hi Mr.Carim
In worksheet "Data" range H30:I136 if any value is going more than 2 then the bellow command should not execute. It the value is less than 2 only it should execute
ActiveSheet.ExportAsFixedFormat xlTypePDF, pdf, xlQualityStandard, True, False, , , True
Hi,
I am using the following code to print sheets in PDF format.
Dim a1, b1, aa, bb, i, pdf As String a1 = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet8") b1 = Array("Sheet1", "Sheet2", "Sheet3.a", "Sheet4", "Sheet7", "Sheet8") pdf = ThisWorkbook.FullName 'Get sheet's visible property, and set to xlSheetVisible aa = a1 For i = 0 To UBound(a1) aa(i) = Worksheets(a1(i)).Visible 'Errors if sheet a(i) does not exist. Worksheets(a1(i)).Visible = xlSheetVisible Next i bb = b1 For i = 0 To UBound(b1) bb(i) = Worksheets(b1(i)).Visible 'Errors if sheet a(i) does not exist. Worksheets(b1(i)).Visible = xlSheetVisible Next i If Sheets("Data").Range("C11").Value = "" Then Sheets(a1).Select Else Sheets(b1).Select 'Make sheets grouped. End If ActiveSheet.ExportAsFixedFormat xlTypePDF, pdf, xlQualityStandard, True, False, , , True Sheets(1).Select 'ungroup sheets 'After export hide sheets For i = 0 To UBound(a1) Worksheets(a1(i)).Visible = False Next i For i = 0 To UBound(b1) Worksheets(b1(i)).Visible = False Next i End Sub
I am having values updating Sheet "Data" and range is H30:I136. If the value >= 2 then it should not print the pdf document and message to be display " value is exceeded"
can any one help me to this condition
Hi every one,
I am new on this forum.
I have two excel files. one is "Consolidated History.xlsx" and other on is "Test.xlsx"
I created one command button on Test.xlsx and type the name (Senthil Kumar P) in cell "C2". when i press the command button, in "Consolidated History.xlsx" (sheet is Consolidated History) it needs to copy entire row only the name entered in Test.xlsx C2 and paste to "Consolidated History.xlsx" Sheet!1 A150.
Both the excel files attached here.
Anyone please send me the code for the above condition
Hi every one,
I am new on this forum.
I have two excel files. one is "Consolidated History.xlsx" and other on is "Test.xlsx"
I created one command button on Test.xlsx and type the name (Senthil Kumar P) in cell "C2". when i press the command button, in "Consolidated History.xlsx" (sheet is Consolidated History) it needs to copy entire row only the name entered in Test.xlsx C2 and paste to "Consolidated History.xlsx" A150.
Both the excel files attached here.
Anyone please send me the code for the above condition