VBA to change cell color based on existing cell color

[Lil Stinker]

Is it possible to change the interior color of a cell based on its existing color?

On Sheet 1 I have a range of cells from B24:S38 where when you double click the cell, the cell color changes from yellow to red and back to clear. Sheet 2 is where the formats and values for this range is stored. Since Sheet 1 is a form that is filled out and the values and formats change per form entry, I want to know if it is possible to change any cell colored yellow to gray when those details are loaded back to Sheet 1.

The concept behind this is Range B24:S38 contains a list of serial numbers from a check out sheet. Double click any serial number to mark it "in" and color it yellow, double click again to mark it as "damaged" and color it red. Save the form and everything is saved to the record on Sheet 2. As more serial numbers come in, retrieve the data from Sheet 2, load it to Sheet 1 and anything that was previously marked in (yellow) is now grayed out so as one marks in new serial numbers, one can tell the difference between what was previously marked in versus what is current.

Any help would be appreciated!

 

[Mattlake]

Hi

I want to say this is one I can help with (no where close to my understanding) if it helps there is this previous one that works , you may just need to adjust it for your requirements.

VBA to change cell color when double clicked and then change it back when double clicked again

Hi all! My current code changes cell color to yellow with a single click and then changes it back to nothing when clicked again. I was wondering if I can do that this time changing it to green when double clicked and then back to nothing when double clicked again. Here's my current code...

www.mrexcel.com

i can get it to change on a double click to yellow (might be green) and to blue on another double click. would this help?

Regards

Matthew

 

[Lil Stinker]

Hi Matthew,

Thanks for the reply but I actually figured it out with this little For loop below. Sorry if my original explanation was convoluted. I already have the code for cycling through different colors in a double click event which I picked up from that exact link a few months back. I was looking for a way to change an existing cell's color without the double click event.

Say you open an existing order where a user has already double clicked a bunch of cells yellow. I wanted a way to have the previously marked yellow cells turn gray when the form was loaded up again. This way the next user would know which items were already returned.

This seems to do the trick.

Sub colorTEST()
    Dim rng As Range
    Set rng = Sheet1.Range("B24:S38")
    
    For Each cell In rng
    If cell.Interior.ColorIndex = 6 Then cell.Interior.Color = RGB(217, 217, 217) 'gray out previous check-ins
    If cell.Interior.ColorIndex = 3 Then
        cell.Interior.Color = RGB(217, 217, 217)
        cell.Font.Color = RGB(255, 55, 55) 'red font for previous damages
    End If
    Next
End Sub

 

[Mattlake]

darn it, I was so trying to see if I could help someone else with something instead of just asking questions

Glad you have found a solution

 

[Lil Stinker]

Well, maybe you can answer this one for me. I'm not well versed in VBA. The code I posted above seems to work fine in its own module, no errors. However, when I copy it over to another module where I have all my other form macros, I get the error telling me to define "cell" as a variable. Any clue as to why that would be happening when it doesn't ask to define it when it's in its own module?

 

[Joe4]

Well, maybe you can answer this one for me. I'm not well versed in VBA. The code I posted above seems to work fine in its own module, no errors. However, when I copy it over to another module where I have all my other form macros, I get the error telling me to define "cell" as a variable. Any clue as to why that would be happening when it doesn't ask to define it when it's in its own module?

You probably have "Option Explicit" turned on (which is a good idea, by the way!).
It just means that you need to declare all your variables before using them.
See: Option Explicit in Excel VBA

 

[Lil Stinker]

Yep, you are correct. I forgot that was there!
Now, at the risk of sounding like a complete noob, if "cell" simply refers to the cell within the range, what would I declare it as?

 

[Joe4]

if "cell" simply refers to the cell within the range, what would I declare it as?

The answer lies within your question!

Dim cell as Range

 

[Lil Stinker]

I need to go to Excel school.
Thank you kindly!

 

[Joe4]

You are welcome.
Glad we were able to help!