VBA to display borders and cell color from a certain validated data list selection

[ArnMan]

Hello,
How would I be able to get VBA to display borders and cell color from a selected item from my validated data list? I have some code listed here. The first part the data validation list works just fine. I am wanting to add a if statement that runs with the selection of "of Capacity". I want I4 to have a thin border yellow interior and "Capacity" as Value or text. and I5 just to have a thin border. and when "of Reading" is selected, those two cells, I4 and I5 clear.

The second half of my code, doesnt seem to display the borders.

With Sheet1.Range("E5").Validation
        .Delete
        .Add Type:=xlValidateList, Operator:=xlBetween, Formula1:="of Reading," & _
                "of Capacity"
                
    End With
    
    Set PlusLabel = Sheet1.Range("I4")
    Set Plus = Sheet1.Range("I5")
    
If Sheet1.Range("E5").Value = "of Capacity" Then
   PlusLabel.BorderAround xlContinuous
   PlusLabel.HorizontalAlignment = xlCenter
   PlusLabel.Interior.Color = Yellow
   PlusLabel.Value = "Capacity"
    
   Plus.BorderAround xlContinuous
   Plus.HorizontalAlignment = xlCenter

Else: Sheets("Sheet1").Range("I4:I5").Clear
End If

I've tried a couple of things, but cant get it to work. I dont want to use conditional formatting since 98% of everything ran by VBA.

Thank you in advance.

 

[Rick Rothstein]

Re: How would I be able to get VBA to display borders and cell color from a certain validated data list selection?

Does cell E5 actually contain exactly the text "of Capacity"... no space in front of the word "of", no characters after the word "Capacity" and with exactly the letter casing shown?

 

[ArnMan]

Re: How would I be able to get VBA to display borders and cell color from a certain validated data list selection?

On the spreadsheet if I click the e5 cell with of Capacity selected up in the formula bar there are no spaces or chars in front of or behind the text and it shows "of Capacity"

Does cell E5 actually contain exactly the text "of Capacity"... no space in front of the word "of", no characters after the word "Capacity" and with exactly the letter casing shown?

 

[ArnMan]

Re: How would I be able to get VBA to display borders and cell color from a certain validated data list selection?

I put the border code in a seperate module and called it from the If statement and it still doesnt work. Then I tried to put it under my worksheet change section and no luck either. Im going to try a "test" cell on the spreadsheet and play with conditional formatting and see if it works that way too and it works I might just have to use that, even though I'd rather vba it.

 

[ArnMan]

Re: How would I be able to get VBA to display borders and cell color from a certain validated data list selection?

Conditional formatting does work.

I'd rather not use it unless i really have to.

I put the border code in a seperate module and called it from the If statement and it still doesnt work. Then I tried to put it under my worksheet change section and no luck either. Im going to try a "test" cell on the spreadsheet and play with conditional formatting and see if it works that way too and it works I might just have to use that, even though I'd rather vba it.

 

[ArnMan]

Re: How would I be able to get VBA to display borders and cell color from a certain validated data list selection?

Ok i've tried the Conditional formatting. And that doesnt work but not because of the If Statement.

Once I've set the conditional format, I run my module. the first thing this module does, since many modules access the same row, it .clear s all the ranges of the previous module cell setups since they are different, which also deletes the conditional format for the current cell I am working on.
Now I can run

PlusLabel.Value = "=If(E5=""of Capacity"", ""Capacity"","""")"

And that works, it reads the Validation list selection of "of Capacity" just fine. But my IF statements for borders and colors still doesnt work.

The Conditional formatting only works if the Target cell is outside of the Range my module works in.

I think my If statement isnt firing off. I added

MsgBox "of Capacity"

also in the "then" part... just to see if it would run but it doesnt bring up a Msg box.

Does it matter if E5 through G5 are merged?

Does cell E5 actually contain exactly the text "of Capacity"... no space in front of the word "of", no characters after the word "Capacity" and with exactly the letter casing shown?

 

[ArnMan]

Re: How would I be able to get VBA to display borders and cell color from a certain validated data list selection?

Update I believe I have figured it out.
Apparently the if statement fires off before any selection if made, therefore since no selection is made it doesn't run anything.
I did how every got it to work, in the SelectionChange setup. I have altered my code for that just a bit. Its not instantaneous like I want. I have to click outside the data validation box in order for the borders and colors to appear, but it is working. If I can get it to do it right when the selection is selected I'll be happy.

Dim celltxt As String
celltxt = ActiveSheet.Range("E5").Text
If InStr(1, celltxt, "of Capacity") Then
Dim PlusLabel As Range
    Dim Plus As Range
    Set PlusLabel = Sheet1.Range("I4")
    Set Plus = Sheet1.Range("I5")
    PlusLabel.BorderAround xlContinuous
    PlusLabel.HorizontalAlignment = xlCenter
    PlusLabel.Interior.Color = Yellow
    PlusLabel.Value = "Capacity"
    Plus.BorderAround xlContinuous
    Plus.HorizontalAlignment = xlCenter
    
Else
Sheets("Sheet1").Range("I4:I5").Clear
End If

But for now Thank you very much for your help.