Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 5th, 2011, 02:36 PM
Registered User
 
Join Date: Jul 2011
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default checking status and changing cell colour

I need to change color based on status of the machine either using VBA or conditional format.
Let me show you some code here and details:
I have machines from D001 to D066 and TM01 to TM99 and T100 to T200 which are defined in Excel sheet like:
D001
D002
Up to
D066
Then
TM01
TM02
Up to
TM99
Then
T100
T101
Upto
T200

Almost each and every machine is having machines call neighbor machine so sheet is setup like this
ATM CMD Level of Priority Other ATM/CDM Machine in Same Branch
2 3 4
D001 1 D045 TM06
D002 2 TM42
D003 3 TM66
D004 2 TM27
D005 1 D047 TM49
D006 2 D060 TM71
D007 3 TM45
D008 1 D046 TM96 T105
D009 3 TM28
D010 3 TM30
D011 1 T106 T107 T113

I want to check it in two ways.
Suppose D001 is having some issue. VBA code or Conditional format will check D001 and then Outage (Partial / Full) in other cell ’J’ and Status (Resolved / Pending / or may be no issue was reported) in Cell ‘X’
If Status is pending and outage is Partial then D001 in A will be D001 but if Status is Pending and outage is full the D001 will appear like D001
This is also possible the machine was reported for partial outage 1 or 2 or 3 times but last time it was reported as Full outage so in this case all previous entry should appear as D001 even it is partial outage. (Reversal should be in same way also. Like full outage might have fixed but partial problem is still pending)
ATM CMD Level of Priority Other ATM/CDM Machine in Same Branch
2 3 4
D001 1 D045 TM06
D001 1 D045 TM06
D001 1 D045 TM06
D001 1 D045 TM06
D001 1 D045 TM06
D001 1 D045 TM06
Above is only for Cell ‘A’
Now 2nd issue is:
As I have more than 1 machine in same place. So, I can keep track of each and every machine rather than scrolling up & down.
e. g.
ATM CMD Level of Priority Other ATM/CDM Machine in Same Branch
2 3 4
D001 1 D002 TM01
D002 1 D001 TM01
D003 1 D005
D003 D005
D004 1
D005 1 D003
TM01 1 D001 D002

Now if TM01 is partial down then it show report in D001 row being a neighbor that TM01 is partially out of order.
Same way, if D002 is fully out then it should report on its neighbor and root. D003 is out partially for 2 different issues.
Please let me know if any confusion. And I don’t mind if problem is solved using VBA but honestly I’m not good enough in VBA.

cell I is having drop doen menu where all sort of issues are included and user will be selecting is accordingly. cell J is usinf forluma:

=IF(ISERROR(LOOKUP(I16,{"Bna","Camera","Capturing cards","Card Reader","Cash Handler","Communication","Dispenser","Display","DV-RW","hardware","Journal Printer","low on Cash","Negative Balance","No Transactions","Out of Cash","Pick Failure","Receipt Printer","Software","Wrong Value"},{"Partial","Partial","Full","Full","Full", "Full","Partial","Full","Partial","Full","Full ","P artial","Partial","F ull","Full","Partial","Partial","Full","Partial"}) ),"",LOOKUP(I16,{"Bna","Camera","Capturing cards","Card Reader","Cash Handler","Communication","Dispenser","Display","DV-RW","hardware","Journal Printer","low on Cash","Negative Balance","No Transactions","Out of Cash","Pick Failure","Receipt Printer","Software","Wrong Value"},{"Partial","Partial","Full","Full","Full", "Full","Partial","Full","Partial","Full","Full ","P artial","Partial","F ull","Full","Partial","Partial","Full","Partial"}) )

and filling cell J with 'Full' or 'Partial'

cell X is having formula:

=IF(AND(L5<>"",V5<>""),"Resolved",IF(L5<>"","Pendi ng",""))

and conditional format (to change colour red if Pending or colour Green if resovled). cell L is time when machine is down which user is entering manually and cell V is having up time (time when issue is fixed) and user it entering it manually again.

in above post I need to set colour on cell A where all machines are mentioned.

thanks and waiting for your reply to solve my issue.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Changing Datagrid Colour based on the Value vuyiswamb VB.NET 2002/2003 Basics 0 May 22nd, 2008 02:24 AM
Changing a label's color (a.k.a. colour) ? Martin Woodhouse C# 2008 aka C# 3.0 7 May 8th, 2008 09:45 PM
Table row background colour changing meetravig Java GUI 0 September 21st, 2007 05:06 AM
Changing Fore colour and back colour darrenb Access VBA 2 May 25th, 2007 07:10 AM
how to change the colour of a cell in a jtable Tomi Java GUI 1 April 25th, 2007 03:35 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.