Solved: If / Statement

[gimli]

Hey yas,

The below statement...what would be the code to do nothing? ? ? thanks for the help



If Range("CB12") = "CUSTOM" And [E12].Interior.ColorIndex = 36 Then

Do nothing

Else: Range("E12") = " "
[E12].Locked = False
[E12].Interior.ColorIndex = 36

end if

[Bob Phillips]

If Range("CB12").Value2 <> "CUSTOM" Or Range("E12").Interior.ColorIndex <> 36 Then
With Range("E12")

.Value2 = ""
.Locked = False
.Interior.ColorIndex = 36
End With
End If

[gimli]

Thanks...how would I incoporate a few more ifs? This doesnt seem to take.

thanks much


If Range("CB12").Value2 <> "CUSTOM" Or Range("E12").Interior.ColorIndex <> 36 Then
With Range("E12")

.Value2 = ""
.Locked = False
.Interior.ColorIndex = 36
End With

ElseIf Range("F13") = 0 Then
Range("E13") = " "
[E13].Locked = True
[E13].Interior.ColorIndex = 39

ElseIf Range("F13") >= 24 And Range("CB12") <> "A4" Then
Range("E13") = "A4 ONLY"
[E13].Locked = True
[E13].Interior.ColorIndex = 39


End If

[Bob Phillips]

Show us the decision logic in pseudo-code.

[gimli]

Ok..heres my problem..cant seem to figure out how to do this..

this code is almost what I need except for the "custom" portion. The first time the sub runs it works fine. It puts a space in E12 unlocks it and changes the color.

The problem is..when the sub runs again...its overwriting data plugged into the cell after the sub ran the first time. I cant figure out how to avoid that. I just want the "CUSTOM" portion to work once. Then when another value chosen for CB12 it will reset so when "CUSTOM" is chosen again it will work one time again.

clear as mud I know..

thanks much




Sub MEL()
' ************
' This sub is hurting my head
' ************

ActiveSheet.Unprotect "test"
If Range("CB12") = "CUSTOM" Then
Range("E12") = " "
[E12].Locked = False
[E12].Interior.ColorIndex = 36

ElseIf Range("F12") = 0 Then
Range("E12") = " "
[E12].Locked = True
[E12].Interior.ColorIndex = 39

ElseIf Range("F13") >= 24 And Range("CB12") <> "A4" Then
Range("E12") = "A4 ONLY"
[E12].Locked = True
[E12].Interior.ColorIndex = 39

ElseIf Range("F13") >= 24 And Range("CB12") = "A4" Then
Range("E12") = Range("$CG$17")
[E12].Locked = True
[E12].Interior.ColorIndex = 39

ElseIf Range("CB12") = "DIN" Then
Range("E12") = Range("DD14")
[E12].Locked = True
[E12].Interior.ColorIndex = 39



Else: Range("E12") = Range("$CG$14")
[E12].Locked = True
[E12].Interior.ColorIndex = 39
End If
ActiveSheet.Protect "test"
End Sub

[Bob Phillips]

How about



Sub MEL()
' ************
' This sub is hurting my head
' ************

ActiveSheet.Unprotect "test"
If Range("CB12") = "CUSTOM" Then

If Range("E12").Interior.Colorindex <> 36 Then

Range("E12") = " "
[E12].Locked = False
[E12].Interior.ColorIndex = 36
End If

ElseIf Range("F12") = 0 Then
Range("E12") = " "
[E12].Locked = True
[E12].Interior.ColorIndex = 39

ElseIf Range("F13") >= 24 And Range("CB12") <> "A4" Then
Range("E12") = "A4 ONLY"
[E12].Locked = True
[E12].Interior.ColorIndex = 39

ElseIf Range("F13") >= 24 And Range("CB12") = "A4" Then
Range("E12") = Range("$CG$17")
[E12].Locked = True
[E12].Interior.ColorIndex = 39

ElseIf Range("CB12") = "DIN" Then
Range("E12") = Range("DD14")
[E12].Locked = True
[E12].Interior.ColorIndex = 39



Else: Range("E12") = Range("$CG$14")
[E12].Locked = True
[E12].Interior.ColorIndex = 39
End If
ActiveSheet.Protect "test"
End Sub

[gimli]

I check it out monday.
TGIF

thanks much

[gimli]

Good Morning,

Ok..below gives me what I need. Thanks much for the help.


Sub mel()

If Range("F12") = 0 Then
Range("E12") = " "
[E12].Locked = True
[E12].Interior.ColorIndex = 39

ElseIf Range("CB12") = "CUSTOM" And Range("E12").Interior.ColorIndex <> 36 Then
Range("E12") = " "
[E12].Locked = False
[E12].Interior.ColorIndex = 36

ElseIf Range("F13") >= 24 And Range("CB12") <> "A4" Then
Range("E12") = "A4 ONLY"
[E12].Locked = True
[E12].Interior.ColorIndex = 39

ElseIf Range("F13") >= 24 And Range("CB12") = "A4" Then
Range("E12") = Range("$CG$17")
[E12].Locked = True
[E12].Interior.ColorIndex = 39

ElseIf Range("CB12") = "DIN" Then
Range("E12") = Range("DD14")
[E12].Locked = True
[E12].Interior.ColorIndex = 39

Else: Range("E12") = Range("$CG$14")
[E12].Locked = True
[E12].Interior.ColorIndex = 39
End If

End Sub