• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Change the background color

T

TPR

Member
Dear Friends,

In a sheet I have various texts. Now I want to colour the cells of texts which I want to keep. I want that when I change the background colours of texts, same cells carrying exactly same ( not partly ) text will turn into same colour.

As example, if I turn cells carrying "Cycle Avon" & "Cycle BSA" into Red, then all cells of that sheet carrying the same texts will turn into red. But the cells carrying only "Cycle" or "BSA" or "Avon" remains same, those will not turn into red.

Is it possible in Excel Friends ? If Yes, then pls help. It would be a gr8 help.

Thanks in advance.

Regards
TRP
 
Hi,

I believe I have a solution for you, but it requires VBA so perhaps that is not what you want.
In any case, here you go.
You may need to change the range to fit your case but for this sample I went with ".usedrange".

It will trigger as soon as you exit the currently selected cell...

Let me know if that helps.
 

Attachments

  • Chandoo.xlsm
    15.5 KB · Views: 10
Hi Pcosta,

Nice, this correct or wrong please guide me thanks.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Static PreviousSelection As String
Dim c As Range
If PreviousSelection <> "" Then
For Each c In ActiveSheet.UsedRange
If c = Range("A1:p32") Then
c.Interior.ColorIndex = Range("A1:p32").Interior.ColorIndex = 3
End If
Next c
End If
Range("A1:p32") = "AAAA"

End Sub
 
JawaharPrem said:
Hi Pcosta,

Nice, this correct or wrong please guide me thanks.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Static PreviousSelection As String
Dim c As Range
If PreviousSelection <> "" Then
For Each c In ActiveSheet.UsedRange
If c = Range("A1:p32") Then
c.Interior.ColorIndex = Range("A1:p32").Interior.ColorIndex = 3
End If
Next c
End If
Range("A1:p32") = "AAAA"

End Sub
Click to expand...
Actually, the part you may want to change is "ActiveSheet.UsedRange". For "A1-P32" it should be:
Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    On Error Resume Next
    Static PreviousSelection As String
    Dim c As Range

    If PreviousSelection <> "" Then
        For Each c In Range("A1:P32")
            If c = Range(PreviousSelection) Then
                c.Interior.ColorIndex = Range(PreviousSelection).Interior.ColorIndex
            End If
        Next c
    End If

    PreviousSelection = Target.Address
 
End Sub
 
PCosta87 said:
Actually, the part you may want to change is "ActiveSheet.UsedRange". For "A1-P32" it should be:
Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    On Error Resume Next
    Static PreviousSelection As String
    Dim c As Range

    If PreviousSelection <> "" Then
        For Each c In Range("A1:P32")
            If c = Range(PreviousSelection) Then
                c.Interior.ColorIndex = Range(PreviousSelection).Interior.ColorIndex
            End If
        Next c
    End If

    PreviousSelection = Target.Address

End Sub
Click to expand...
PCosta87 said:
Hi,

I believe I have a solution for you, but it requires VBA so perhaps that is not what you want.
In any case, here you go.
You may need to change the range to fit your case but for this sample I went with ".usedrange".

It will trigger as soon as you exit the currently selected cell...

Let me know if that helps.
Click to expand...
PCosta87 said:
Hi,

I believe I have a solution for you, but it requires VBA so perhaps that is not what you want.
In any case, here you go.
You may need to change the range to fit your case but for this sample I went with ".usedrange".

It will trigger as soon as you exit the currently selected cell...

Let me know if that helps.
Click to expand...
Hi Friend,

In your workbook it works perfect, but can't understand how can I use the code in my workbook.
Pls help.
Regards
TRP
 
TPR said:
Hi Friend,

In your workbook it works perfect, but can't understand how can I use the code in my workbook.
Pls help.
Regards
TRP
Click to expand...
Hi,

That code needs to be pasted in the code section of the sheet you wish to use and not in a module, as I'm assuming you were trying to do. Simply change the range to the one you need by replacing "Range("A1-P32")" and you are good to go:
1.gif

Let me know if you have any further questions.
 
PCosta87 said:
Hi,

That code needs to be pasted in the code section of the sheet you wish to use and not in a module, as I'm assuming you were trying to do. Simply change the range to the one you need by replacing "Range("A1-P32")" and you are good to go:
View attachment 42102

Let me know if you have any further questions.
Click to expand...
Hi,

Am amazed, it worked fine !!! Hats off.

Regards
TPR
 
You must log in or register to reply here.
Back
Top