Drop Down Box to affect Formatting of Cell

[djp203]

Hi,

not sure how to approach this:

I would like for there to be a drop down box with answer yes or no (this part is fine - data validation) then further down the row there will be numbers, which if Yes is selected I would like to have brackets around the number.

Any way of doing this?!

Numbers used are purely for example and can be any whole number value...

Yes..............................(0).(5).(2).(2).(4)....................
No................................0.0.2.0.4.0.5.0.1.................

 

[MickG]

Hi, If you have a number of (Validation Boxes) in column "A" and your cell of numbers seperated by "Fullstops" in column "C " then this code will Place/Remove Brackets depending on whether column "A" row is a "Yes" or "No".(With No inverted commas).
NB:- Run code first with all Boxes in "A" showing "Yes"

Sub Brac()
Dim Rng As Range, Dn As Range, Ray, num
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
For Each Dn In Rng
Ray = Split(Dn.Offset(, 2), ".")
    If Dn = "Yes" And Not Left(Dn.Offset(, 2), 1) = "(" Then
        For num = 0 To UBound(Ray)
            Ray(num) = "(" & Ray(num) & ")"
        Next num
            Dn.Offset(, 2) = Join(Ray, ".")
    ElseIf Dn = "No" And Left(Dn.Offset(, 2), 1) = "(" Then
        For num = 0 To UBound(Ray)
            Ray(num) = Mid(Ray(num), 2, Len(Ray(num)) - 2)
        Next num
            Dn.Offset(, 2) = Join(Ray, ".")
End If

Next Dn

Regards Mick

 

[djp203]

Hi, If you have a number of (Validation Boxes) in column "A" and your cell of numbers seperated by "Fullstops" in column "C " then this code will Place/Remove Brackets depending on whether column "A" row is a "Yes" or "No".(With No inverted commas).
NB:- Run code first with all Boxes in "A" showing "Yes"

Sub Brac()
Dim Rng As Range, Dn As Range, Ray, num
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
For Each Dn In Rng
Ray = Split(Dn.Offset(, 2), ".")
    If Dn = "Yes" And Not Left(Dn.Offset(, 2), 1) = "(" Then
        For num = 0 To UBound(Ray)
            Ray(num) = "(" & Ray(num) & ")"
        Next num
            Dn.Offset(, 2) = Join(Ray, ".")
    ElseIf Dn = "No" And Left(Dn.Offset(, 2), 1) = "(" Then
        For num = 0 To UBound(Ray)
            Ray(num) = Mid(Ray(num), 2, Len(Ray(num)) - 2)
        Next num
            Dn.Offset(, 2) = Join(Ray, ".")
End If
 
Next Dn

Regards Mick

Hi Mick, thanks for that - I'm trying to step my way through it to have a clue what any of that means!!!

I think I may have not helped matters whatsoever in the way I showed the arrangement

Column A would have the Valadation Boxes, then Columns B onwards would have numbers, no full stops. I was just trying to show that the numbers were in sepearte cells which obviously didn't help.

I've tried downloading the software to upload my excel stuff but this comp has lots of restrictions

 

[MickG]

Hi, I understand, hopefully. I'll try and rework it.
Regards Mick

 

[lenze]

Define a Custom Style.(say "myBracs") Format>Styles. Custom format the number as (#). Now say your data is in Coulmns B through F. You can use this change event

Private Sub WorkSheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If UCase(Target) = "YES" Then
    Range(Cells(Target.Row, "B"), Cells(Target.Row, "F")).Style = "myBracs"
Else: Range(Cells(Target.Row, "B"), Cells(Target.Row, "F")).Style = "Normal"
End If
End Sub

lenze

 

[MickG]

Hi, Try this. The code will bracket/unbracket cells in column (B) on, depending on "Yes" or "No" in column "A".

Sub brac2()
Dim Rng As Range, RngAc As Range, Ac As Range, Dn As Range, Rw As String
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))

For Each Dn In Rng
Rw = Split(Dn.Address, "$")(2)
 Set RngAc = Range(Range("B" & Rw), Cells(Rw, Columns.Count).End(xlToLeft))
        For Each Ac In RngAc
            If Dn.Value = "Yes" And Not Left(Ac, 1) = Chr(40) Then
                Ac.Value = "'" & Chr(40) & Ac & Chr(41)
            ElseIf Dn.Value = "No" And Left(Ac, 1) = Chr(40) Then
                Ac.Value = Mid(Ac, 2, Len(Ac) - 2)
            End If
        Next Ac
Next Dn
End Sub

Regards Mick

 

[djp203]

Cheers for that!