• 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.

Conditional format with a specific value in a column

Is there a macro conditional formatting on a let say column A1:A60,


A value of 100, 105, 300, 350 and other than these value find MSG BOX: Do you want to delete “Yes” or “no”





Something like this or any idea.





Thanks.
 

rahulshewale1

Active Member
hi @RDEXCEL2020 ,

Test This ?
Code:
Option Explicit

Sub Test()
Dim mrng, Mycell As Range
Set mrng = Range("A1:A60")

Dim msgResult As VbMsgBoxResult

For Each Mycell In mrng

    If Mycell.Value = 100 Or Mycell.Value = 105 Or Mycell.Value = 300 Or Mycell.Value = 350 Then
      Else
        
        Mycell.Interior.Color = vbYellow
        msgResult = MsgBox("Do you want Delete Value", vbYesNo)
        If msgResult = vbYes Then
            Mycell.Value = ""
        End If
    End If

Next
End Sub
 

Attachments

I tried and it work exactly how it supposed to be doing.

How do I get out in the message box, If I decided to get out there? " Yes or no.
My list are so long and its had to get out of there, I have to keep pressing yes/no button all the way to the end.

Also, this code: Mycell.Interior.Color = vbYellow ...
can you fix to a single cell where the cursor is ? It highlight yellow the entire 1:60 and I don't the color cell to stay in the cell. Because I have reformat the cell to remove the color yellow.

Thanks.
 
Last edited:

rahulshewale1

Active Member
hi @RDEXCEL2020 ,

See if is it ok ?

Select The Range Then Run The Code.

Code:
Sub test()
Dim mrng, Mycell As Range
Set mrng = Selection

For Each Mycell In mrng

    If Mycell.Value = 100 Or Mycell.Value = 105 Or Mycell.Value = 300 Or Mycell.Value = 350 Then
    ' DO nothing
    Else
        Mycell.Value = ""
    End If

Next
End Sub
 

Attachments

Hi,
Sorry....

I like the other version because the cursor goes in the cell that is not 100, 105, 300 and 350. Also I was able to delete and go to the next one. The only issue is that the yellow stays in the cell and never disappear. I like that there is a message box and ask if I want to delete yes/no but the problem if I decide to exit, I was not able to exit. I have to keep clicking all the way to end to exit out.

Thanks.
 
Last edited:
Hi
Here is the other version. If you can fix just yellow tab to not stay in the cells and add a "exit" in the message box... I think everything will be great.

Thank you
 

Attachments

Hi
Where to “exit”? I guess no specific cell. Just when i decided to exit. Then when i am ready to start again then i just click the button.

I just though of this when it search then msg box: “what you want to do”
“Correct” or “Exit”

Correct - maybe a box to enter the correct number then “done” then it will go on to the next search
When its finish then it will will say “your search finished”

“Exit” - just exit out in the message box to stop searching.

What you think? If not possible its ok.
I just thought it maybe easier .

Thanks
 
Then maybe the vbYellow disappear upon exiting? I noticed the yellow stayed in the cells. So when i exit the msg box yellow goes away.

Thanks
 
Top