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.