Hi Swapnil,
I have attached a text document with the VBA macro code which addresses your
problem. All you need to do is copy+paste the code under Tools>Macro>Visual
Basic Editor in Sheet1.
I have tested the same here and it seems to do what you need. I am using Excel
2003.
Try entering the values for the mentioned scenarios and confirm if the same is
working.
Regards.
Serghei.
From: swapnilp1...@gmail.com
Date: Thu, 30 Jul 2009 19:55:30 +0530
Subject: $$Excel-Macros$$ Re: Please help me to solve this problem
To: excel-macros@googlegroups.com
Hi Serghei,
Thanks for giving reply.
I saw you excel, but it is not solving my problem. I am not able to find macro
written in your excel.
I want to apply conditional formatting on Major suppliers columns "PP" and "PE"
i.e. column M and N
Suppose If I insert supplier name in "PP" column (i.e. cell "M5"), then it will
check that whether the total consumption of pp products (i.e. B5:E5) is > 0 and
also it will check that import (i.e. "L5") is > 0 if both these conditions are
true then it will not give any error (that means I can insert supplier name in
that cell - "M5"). But if one of these conditions is false, that cell (i.e.
"M5") should get fill with red colour which will indicate that user cannot
insert supplier name if consumption or import is 0.
I hope you got my point. Actually it is little difficult to explained.
Thanks and best regards,
Swapnil.
On Thu, Jul 30, 2009 at 7:28 PM, Serghei Ovanesov <oserg...@hotmail.com> wrote:
Hi Swapnil,
I am not sure if this is what you wanted, but here it goes. Unfortunately I was
not able to replicate the same with conditional formatting, thus i had to
resort to VBA macro.
If anyone is able to do the same with conditional formatting please do post.
Find the VBA solution attached and do let me know if this has achieved your
goal.
Kind regards.
Serghei.
From: swapnilp1...@gmail.com
Date: Thu, 30 Jul 2009 11:17:35 +0530
Subject: $$Excel-Macros$$ Please help me to solve this problem
To: excel-macros@googlegroups.com
Hi All,
I want to do conditional formatting on selected cells based on values inserted
in other cells.
I have explained the problem in attached excel. If you still have any query let
me know I will try to explain it again.
Thanks and Best regards,
Swapnil.
_________________________________________________________________
Windows Live Messenger: Happy 10-Year Anniversary—get free winks and emoticons.
http://clk.atdmt.com/UKM/go/157562755/direct/01/
--~--~---------~--~----~------------~-------~--~----~
----------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 5,200 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~----------~----~----~----~------~----~------~--~---
Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
'When Imported column is updated do the following validations
If Target.Column = 12 Then
If Target.Value > 0 Then
Call CheckMajorSuppPP(Target)
Call CheckMajorSuppPE(Target)
ElseIf Target.Value = 0 Or Target.Value = "" Then
If IsEmpty(Cells(Target.Row, Target.Column + 1).Value) = False Then
Call CheckConsumptionPP(Target)
End If
If IsEmpty(Cells(Target.Row, Target.Column + 2).Value) = False Then
Call CheckConsumptionPE(Target)
End If
End If
End If
'Check if the user has rectified previously incorrect entries
Call CheckMajorSuppliesChanges(Target)
'Check the sum of consumtion and check PP/PE entries
Call CheckConsumptionPP(Target)
Call CheckConsumptionPE(Target)
Application.EnableEvents = True
Exit Sub
End Sub
Sub CheckConsumptionPE(ByVal Target)
'Check Consumption of PE
If (Cells(Target.Row, 6).Value + Cells(Target.Row, 7).Value +
Cells(Target.Row, 8).Value _
+ Cells(Target.Row, 9).Value + Cells(Target.Row, 10).Value) = 0 Then
If IsEmpty(Cells(Target.Row, 14)) = False Then
With Cells(Target.Row, 14)
.Interior.ColorIndex = 3
End With
With Cells(Target.Row, 14).Borders
.LineStyle = xlContinuous
.Weight = xlThin
End With
Else
With Cells(Target.Row, 14)
.Interior.ColorIndex = 2
End With
With Cells(Target.Row, 14).Borders
.LineStyle = xlContinuous
.Weight = xlThin
End With
End If
Else
With Cells(Target.Row, 14)
.Interior.ColorIndex = 2
End With
With Cells(Target.Row, 14).Borders
.LineStyle = xlContinuous
.Weight = xlThin
End With
End If
End Sub
Sub CheckConsumptionPP(ByVal Target)
'Check Consumption of PP
If (Cells(Target.Row, 2).Value + Cells(Target.Row, 3).Value +
Cells(Target.Row, 4).Value _
+ Cells(Target.Row, 5).Value) = 0 Then
If IsEmpty(Cells(Target.Row, 13)) = False Then
With Cells(Target.Row, 13)
.Interior.ColorIndex = 3
End With
With Cells(Target.Row, 13).Borders
.LineStyle = xlContinuous
.Weight = xlThin
End With
Else
With Cells(Target.Row, 13)
.Interior.ColorIndex = 2
End With
With Cells(Target.Row, 13).Borders
.LineStyle = xlContinuous
.Weight = xlThin
End With
End If
Else
With Cells(Target.Row, 13)
.Interior.ColorIndex = 2
End With
With Cells(Target.Row, 13).Borders
.LineStyle = xlContinuous
.Weight = xlThin
End With
End If
End Sub
Sub CheckMajorSuppPP(ByVal Target)
If IsEmpty(Cells(Target.Row, Target.Column + 1).Value) = False Then
Cells(Target.Row, Target.Column + 1).Interior.ColorIndex = 2
With Cells(Target.Row, Target.Column + 1).Borders
.LineStyle = xlContinuous
.Weight = xlThin
End With
Else
Cells(Target.Row, Target.Column + 1).Interior.ColorIndex = 3
With Cells(Target.Row, Target.Column + 1).Borders
.LineStyle = xlContinuous
.Weight = xlThin
End With
End If
End Sub
Sub CheckMajorSuppPE(ByVal Target)
If IsEmpty(Cells(Target.Row, Target.Column + 2).Value) = False Then
Cells(Target.Row, Target.Column + 2).Interior.ColorIndex = 2
With Cells(Target.Row, Target.Column + 2).Borders
.LineStyle = xlContinuous
.Weight = xlThin
End With
Else
Cells(Target.Row, Target.Column + 2).Interior.ColorIndex = 3
With Cells(Target.Row, Target.Column + 2).Borders
.LineStyle = xlContinuous
.Weight = xlThin
End With
End If
End Sub
Sub CheckMajorSuppliesChanges(ByVal Target)
If Target.Column = 13 Or Target.Column = 14 And Not Cells(Target.Row, 12) >
0 Then
If IsEmpty(Target.Value) = False Then
Cells(Target.Row, Target.Column).Interior.ColorIndex = 2
With Cells(Target.Row, Target.Column).Borders
.LineStyle = xlContinuous
.Weight = xlThin
End With
ElseIf IsEmpty(Target.Value) = True _
And (IsEmpty(Cells(Target.Row, 12).Value) Or (Cells(Target.Row,
12).Value = 0)) Then
Cells(Target.Row, Target.Column).Interior.ColorIndex = 2
With Cells(Target.Row, Target.Column).Borders
.LineStyle = xlContinuous
.Weight = xlThin
End With
End If
End If
End Sub
