How can I combine to macros?

[akinkaraman]

I have 2 macros. One of them searches a value and find the searched values in the sheet and shows them as in a different colored cell. The other man cleans the colored searched cells.

What I want is to combine these 2 macros into 1 macro which will ask if I want to search a value or clean the colored cells.

Could you please help me to combine these 2 macros?

Search-Find-Color Macro

Sub Find_Color()

    Dim sor As String, c As Range, Adr As String

    sor = Application.InputBox("What are you looking for?", "Search")

    If sor = "" Then Exit Sub

    Cells.Interior.ColorIndex = 0

    With Cells
        Set c = .Find(sor, , xlValues, xlWhole)
        If Not c Is Nothing Then
            Adr = c.Address
            Do
                c.Interior.Color = 49407
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> Adr
        End If
    End With

End Sub

Clean the color

Sub Clean_Color()
    Cells.Interior.ColorIndex = 0
End Sub

Thanks in Advance..

 

[wudixin96]

Sub Find0rClean_Color()
    Dim myChoice
    myChoice = InputBox("Input 1 or 2." & vbCrLf & "1 for FindColor" & vbCrLf & "2 for CleanColor", "Your Choice", 1)
    Select Case myChoice
        Case "1"
            Dim sor As String, c As Range, Adr As String
            sor = Application.InputBox("What are you looking for?", "Search")
            If sor = "" Then Exit Sub
            Cells.Interior.ColorIndex = 0
            With Cells
                Set c = .Find(sor, , xlValues, xlWhole)
                If Not c Is Nothing Then
                    Adr = c.Address
                    Do
                        c.Interior.Color = 49407
                        Set c = .FindNext(c)
                    Loop While Not c Is Nothing And c.Address <> Adr
                End If
            End With
            MsgBox "Find Color Completed !", vbInformation + vbOKOnly, "haha"
        Case "2"
            Cells.Interior.ColorIndex = 0
            MsgBox "Clean Color Completed !", vbInformation + vbOKOnly, "haha"
        Case Else
            MsgBox "Nothing Happened !", vbInformation + vbOKOnly, "haha"
    End Select
   
End Sub

 

[akinkaraman]

Thank you very much. I am really very appreciated.

 

[akinkaraman]

I have changed the codes a little bit and I need help again.

The code works good.

Such as when I search 132 it finds the cells which has 132

What I am trying to do is :

When I search " *132 ", I want it to find and color the cells which ends with " 132 "
When I search " 132* " , I want it to find and color the cells which starts with " 132 "
and when I search " *132* " , I want it to find and color the cells which starts or ends with " 132 " or has " 132 " inside.

Is it possible?

    Sub Bul_ve_Renklendir()
        Dim Seçim
        Seçim = InputBox("Seçiminizi Girin" & vbCrLf & "Bul ve Renklendir için 1" & vbCrLf & "Renklendirmeleri temizlemek için 2", "Seçiminiz", 1)
        Select Case Seçim
            Case "1"
                Dim Alan As Range
    Kelime = InputBox("Aranacak Değeri Giriniz", "Bul ve Renklendir")
            For Each Alan In ActiveSheet.UsedRange
                If Alan.Value Like Kelime Then
                    Alan.Interior.ColorIndex = 6
                End If
            Next
                MsgBox "Bul ve Renklendir Tamamlandı !", vbInformation + vbOKOnly, "Sonuç"
            Case "2"
                    Cells.Interior.ColorIndex = 0
                MsgBox "Renklendirmeler Temizlendi !", vbInformation + vbOKOnly, "Sonuç"
            Case Else
                MsgBox "Herhangi bir işlem yapılmadı !", vbInformation + vbOKOnly, "Sonuç"
        End Select
   
    End Sub

 

[Luke M]

What you stated is what appears to be working. Do pay attention to the spaces in your search terms...not sure if they are literally there, or you only typed it that way to make it easier to read on this forum. For instance, if I search for:
123*

This is the result

Note that it didn't highlist A3, since the 123 was at the end.

 

[akinkaraman]

It can be 123 cars
It can be Auto 123
It can be 5123
It can be 1238
It can be 512378

That is right if you search 123* It will not highlight " bad 123 " because the cell is not starting with 123 but it will not highlilght 123 , it will be highlighted just if you search 123

 

[Luke M]

If you want to force is so that there has to be at least one character, after/before 123, use the ? character.
So, searching for:
123?*

produces these results:

The * wildcard says anything or nothing beyond this point, while the ? wildcard specifies a character must exist.

 

[akinkaraman]

I see.. Okay, that's fine.

So I have to search ?123 or 123? instead of *123 and 123*, right?

 

[akinkaraman]

Wonderful Luke. It is working like that. Thank you very much.

 

[Luke M]

Awesome, glad I was able to help.

 

[akinkaraman]

Could you please check my file?

I seach as " ?6 " in this example file. It finds and shows -6 but it doesn't find -16 or -122176 and on the orher hand it doesn't show the cells which has already a conditional formating (Q15 and X11)

Plus when I search ?6? It doesn't find 1221675 at AB17

 

[akinkaraman]

I've solved my problems with the search. The only and the last problem; that is not working on the cells which has already got the Conditional Formating.

I want to share my file with the macro for your concern. I wish we can find a solution for the Conditional Formatted cells.

I search *6 and it doesn't detect the cells at E15 and L11 which already has Conditional Formating.

(select 1 for search a value, select 2 for cleaning the colored cells back.)

 

[NARAYANK991]

Hi ,

See your file.

You need to understand that the code is coloring the cell interiors , which is the same as manually formatting them with the desired color ; however , between a cell having a manual format and a conditional format , the conditional format always has priority.

Thus a cell will always display the color which it gets because a CF rule is satisfied , irrespective of what color it may be formatted with , both manually and through VBA.

Narayan

 

[akinkaraman]

I see. So there is no way to make the macro primary conditionally format.

Thanks than. I understood.

Thanks for your helps /wudixin96 , /Luke M and /Narayan