conditional formatting

[mwvirk]

i want to change the cell B1 in red colour by checking the range A1:A300 if its having same value like B1 and if range C1:C300 is 'F' and range D1:D300 is 'D' but if C1:C300 is 'P' then cell B1 should be in yellow colour

e.g. if B1 is ABC then it should look A1:A300 for ABC - when its true then look for C1:C300 for 'F' and D1:D300 'D' - if all true then ABC in B1 should be RED (background colour) and if C1:C300 is 'P' then B1 should be yellow background.

thanks

 

[mwvirk]

A great deal of confusion!
From that information, I don't see how I can be of any assistance.

pls let me know what is confusing you here?
it is like you machine A in 1 branch and same branch is having machine B C D
A B C D
B A C D
C A B D
D A B C
status is also avaibale in next line for all machines with respective neighbour machines. when A is down then it will show in next lines also.

i think macro will be a better idea. alas i'm not good in VBA

Hopefully you will help here.

Thanks.

 

[mwvirk]

i don't know much (or anything) about VBA but what i can feel that reading cell A to down A999 and checking cell J & X for each time can solve 1 issue.
e.g. read cell A1 and check cell J1 (full) and cell X1 (pending) then cell A1 should change colour to Red

for the other issue, read cell A3 and check cell A to A999. once found same value then check cell X & J and change colour for cell A3
e.g. read cell A3 and then check from A1 to A999 - once it found (suppose on A10) then check status of J10 (partial) and then check X10 (pending) then A3 will appear Yellow

same to be done for cell 4 & 5 (since i have max 3 neighbour machines for each machine)

sorry, but you guys will be knowing better solution. since (as i said) i'm 0 in VBA. if Excel use some other code than may be i was able to fix it. but now i need your help.
thanks.

 

[mwvirk]

i got help from my friend for one of the above issue but macro is not working properly on my sheet:

Sub color ()
Dim As Integer myRow
'Sheets ("D001"). Select
For myRow = 2 to 25
Cells (myRow, 24). Activate
If ActiveCell.Text = "Resolved" Then
ActiveCell.Interior.ColorIndex = 4
ElseIf ActiveCell.Text = "Pending" Then
ActiveCell.Interior.ColorIndex = 3
Else
ActiveCell.Interior.ColorIndex = 6
End If
Next myRow
End Sub

thnx

 

[Peter_SSs]

That code has a syntax error. The second line should be

Dim myRow As Integer

A couple of other comments though.

1. This code seems to have little to do with the multiple-condition Conditional Formatting that was being asked for earlier. This is really the reason reason I have been unable to offer much useful advice. The data, layout and requirements seem to change with virtually every post you make.

General coding advice:
2. It is rare that you need to select cells to work with them and selecting cells slows your code considerably.

3. It is not a good idea to name a macro using a word that vba has a special meaning for (color).

In any case, after making the syntax change, does the code do what you want?

 

[mwvirk]

no.it is painting only cell range X whenever it find empty cell.

 

[Peter_SSs]

1. Doesn't it paint green or red if column X contains 'Resolved' or 'Pending'?

2. What did you think it should do?

 

[mwvirk]

no, its not filling with red/green if cell value is Resolved or Pending.
i want to change colour in cell A as red when issue is pending and full outage but if outage is partial and case is pending then cell A should be Yellow.

 

[mwvirk]

any chance to solve this issue

 

[mwvirk]

with the help of some friends, we've prepared some code but it's still not giving 100% output. infact it's giving some error. please help:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Goto Outtahere
Application.EnableEvents = False

Dim myRow As Integer
For myRow = 2 To 25
With Sheets("ATM-CDM Daily Status Report").Cells(myRow, 1)
Select Case Sheets("ATM-CDM Daily Status Report").Cells(myRow, 24).Value
Case "Resolved": .Interior.ColorIndex = 4
Case "Pending": If check() = 1 Then .Interior.ColorIndex = 6 Else End If
Case Else: '.Interior.ColorIndex = 6
End Select
End With
Next myRow



Outtahere:
Application.EnableEvents = True
End Sub

Function check()
If Sheets("ATM-CDM Daily Status Report").Cells(myRow, 10).Value = "partial" Then
check = 1
Else
check = 0
End If
End Function