Solved How to allow data entered only in cells with color

Microsoft Office 2013 professional produ...
July 22, 2015 at 23:22:07
Specs: Windows 8.1, good
I am using excel 2013, I have some conditional formatting that changes certain cells red depending on a number entered in a cell. As the user changes that cell number, from 1-10, different cells change red. I want to somehow, only allow the user to enter a value in one of the red cells. I used data validation to not allow numbers entered outside the ranges that I set for the cells, but can't seem to not allow those ranges to be entered while a cell is normal background color. Any help would be very appreciated as I have been online 3 nights looking for this.

See More: How to allow data entered only in cells with color

Report •

✔ Best Answer
July 27, 2015 at 07:18:47
This code might get you started. Since I don't know the layout of your worksheet nor the process that your users follow to enter data, I can't offer anything specific.

What this code does is check the fill color of a cell as soon as a user enters data. If the cell was not Red when the user entered the data, the data is cleared and a message box is presented.

I assumed that we needed some non-formatted cells to get us started, so the code allows data to be entered into Column A, which would then trigger the Conditional Formatting (Red) in other columns. As I said, I don't know how your spreadsheet works or how it is laid out, so I can only offer a "generic example" at this point.

Store this code in the Sheet module for the sheet in which the changes will be made:

Private Sub Worksheet_Change(ByVal Target As Range)
'Allow entry of data into Column A
  If Target.Column <> 1 Then
'Determine is cell is filled with Red
    If Target.DisplayFormat.Interior.ColorIndex <> 3 Then
'If yes, Disable events, clear the cell, present Message Box
      Application.EnableEvents = False
        Target = ""
        MsgBox "Entries Not Allowed Until Cell Turns Red"
'Re-enable events
      Application.EnableEvents = True
    End If
  End If
End Sub

Two Important items:

1 - You will note the Application.EnableEvents instructions. These are required because the code is changing the sheet by clearing the data from the cell. This change would cause the code to fire again (and again and again and again) so the Events are disabled to allow the code to make the change and then re-enabled so the code only fires when a user makes a change.

The key point to remember here is that if the code disables Events and you stop the code (or it crashes) before Events are re-enabled, the code will not fire until you re-enable Events. This can be done by simply running a quick macro such as this:

Sub Events_Enabled()
  Application.EnableEvents = True
End Sub

2 - As written, the code clears any user entered data if the cell is not Red. However, it does not prevent the user from manually choosing a fill color of red and then entering data. Once again, since I don't know what protecction schemes you are using (if any) so that's about all I can offer at this point.

With some more detail, the code can obviously be refined. The only thing that I ask is that you don't following this pattern:

"I need some code to do this."
"Great, that works. Now can make it do this?
"Cool! One more thing. I want it to do this.

Often, when you try to keep is simple at the beginning and then add more and more requirements, the code ends up getting very messy and inefficient. Sometimes it even needs to be completely rewritten from the start. That is not fun for those of us writing the code. The more details you provide right upfront, the better.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
July 23, 2015 at 06:52:08
I believe that you need to find a workaround to accomplish your goal. By that I mean that you can't restrict entry into cell by directly monitoring whether the cell has been Conditionally Formatted or not, but you may be able restrict entry into the CF cells by checking the cells that caused the CF to triggered.

In other words, if an certain entry in A1 causes B1 to become Red via CF, then the Data Validation for B1 should be monitoring the entry in A1. That keeps the CF and Data Validation as separate operations, but accomplishes the main goal. At least I think it does. ;-)

I haven't played with this too much, but this link contains some examples of Data Validation formulas that monitor the values in other cells and prevents entry to a specific cell if certain conditions in those other cells aren't met.

Down near the bottom of the page is a section entitled:

Use a formula to calculate what is allowed

https://support.office.com/en-nz/ar...

If nothing there works for you, we may need to use some VBA (a macro) to solve your issue. If that is the case, we will need a lot more detail, but let's wait and see if you can use one of the methods found at that site.

Let's us know.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 23, 2015 at 07:20:38
Thanks for your replay Derby. I probably could do this via data validation. I was hoping not to because i was thinking it would be a lengthy process and didnt know if it would be heavy on the processor. I am planning on putting this up on my website. So if that matters which direction i choose to go. I can post the code tonight when i get home from work if need be. I found some vba that recognizes cell color, i just though i could do it that way. So if the sheet changes for any reason, the vba would funtion the same. Thanks again. Oh and by the way, i am fairly new to all the fancy parts of Excel so please excuse my ignorance.

Report •

#3
July 23, 2015 at 10:24:21
No problem on the "ignorance" front. We've all been there! ;-)

Be careful of any code that claims to "recognize cell color". Recognizing a cell's fill color via the [cell].interior.colorindex method is not likely to get you to your desired result.

That method only recognizes actual fill colors, not colors applied via CF. While I have seen some mention of code that claims to recognize CF coloring, the more common method within VBA is simply check the value of the cells that would cause the CF to be triggered. If those values match those that would have triggered the CF, one can assume that CF triggered and we can then let the code deal with unlocking the cell. That is similar to what I suggested earlier when I brought up the Data Validation suggestion.

My main point is that the [cell].interior.colorindex method will not recognize CF coloring.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Related Solutions

#4
July 23, 2015 at 20:38:18
OK Derby, thanks a lot for the info, saved me from losing my mind. I am checking out the link you posted. Hopefully I can find something that can work for my needs. Another question for you if you don't mind, I hate to be a pain but you look like you answer quite a lot of questions from at the posts I've been reading. I am looking for code to have contents on a sheet emailed to the user if they enter their email in a cell or form of some kind. Thank you very much for your guidance. If you have any links for newbies to learn VBA or advance formulas, please post. Thanks again

Report •

#5
July 23, 2015 at 21:00:24
DerbyDad03 & itchiTrigger

Found this:

http://www.excelfox.com/forum/f22/g...

I have not tested it,
so I do not know if it works or not.

DerbyDad03, you should be able to shed more light on the code then me.

MIKE

http://www.skeptic.com/


Report •

#6
July 24, 2015 at 10:22:31
ItchiTrigger,

Since your latest question is not related to the same issue that is being discussed in this thread, it should be posted in it's own thread with a relevant subject line.

As far as Mike's link, I do believe that your solution may be found there. If I am not mistaken, the fairly intricate code posted there was written for Excel 2003 and may not work in 2007 and later. (I could be wrong) However, it appears that the introduction of the DisplayFormat property may be what you are looking for. See the first post on page 2 of that thread.

When I ran the "simple code" below, it returned the ColorIndex number for a cell that was filled via CF. It looks like Microsoft finally heard the cries of the masses and now offers a way to determine the fill color a Conditionally Formatted cell - within reason. A word of caution: It appears that the DisplayFormat.Interior.ColorIndex property returns the correct ColorIndex number for the standard color pallet, but once you start using fill effects and customized colors for your CF, things get a little messy. I'll explain more on that later.

First the simple code...

Conditionally format A1 to use a standard fill color, then run this code after the CF color is showing. The value that is returned will be the Interior.ColorIndex number for that color. That value can be used for other purposes within VBA, such as unlocking a cell to allow user input.

Sub What_CF_Color()
'Get ColorIndex value of cell
  MsgBox Range("A1").DisplayFormat.Interior.ColorIndex
End Sub

Now run this code:

Sub Match_CF_Color()
'Set C1 to match fill color of CF filled cell
    Range("C1").Interior.ColorIndex = _
        Range("A1").DisplayFormat.Interior.ColorIndex
End Sub

Since you used a standard color for the CF in A1, the fill color of C1 should match the CF color of A1.

Now choose a fill effect or custom color for the CF and re-run the code. You may see a different fill color applied to C1 even though the same ColorIndex value from the CF cell is applied to C1.

For example, I used a custom green to CF a cell. The Color Index value that the code returned was 50. When the coe applies that same ColorIndex value of 50 to C1, a different (the standard) green is applied.

Bottom line: It would be best to stick to the standard color pallet when using CF if you are going to be using the CF Interior.ColorIndex value for other purposes.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
July 24, 2015 at 11:02:26
DerbyDad03,

Tried running your code with my Excel 2007 and received the following error code:

Run-time error 438
Object doesn't support this property or method

It would seem that .DisplayFormat is not supported on 2007.

Tried to write your code long hand (not copy & paste)
and .DisplayFormat was not on the dropdown list of commands.

I believe it may only be 2010 or greater. :-(

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#8
July 24, 2015 at 11:53:29
You may be right. This site seems to indicate 2010 and 2013 only.

https://msdn.microsoft.com/en-us/li...

You may have to settle for the long version of the code offered at the site that Mike linked to.

I haven't tested it and don't have time right now.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
July 24, 2015 at 18:31:08
Hey Derby, The code works in 2013. So, that being said, can we make it see that a cell has a color, and create a 'stop' of some sort?

Report •

#10
July 27, 2015 at 07:18:47
✔ Best Answer
This code might get you started. Since I don't know the layout of your worksheet nor the process that your users follow to enter data, I can't offer anything specific.

What this code does is check the fill color of a cell as soon as a user enters data. If the cell was not Red when the user entered the data, the data is cleared and a message box is presented.

I assumed that we needed some non-formatted cells to get us started, so the code allows data to be entered into Column A, which would then trigger the Conditional Formatting (Red) in other columns. As I said, I don't know how your spreadsheet works or how it is laid out, so I can only offer a "generic example" at this point.

Store this code in the Sheet module for the sheet in which the changes will be made:

Private Sub Worksheet_Change(ByVal Target As Range)
'Allow entry of data into Column A
  If Target.Column <> 1 Then
'Determine is cell is filled with Red
    If Target.DisplayFormat.Interior.ColorIndex <> 3 Then
'If yes, Disable events, clear the cell, present Message Box
      Application.EnableEvents = False
        Target = ""
        MsgBox "Entries Not Allowed Until Cell Turns Red"
'Re-enable events
      Application.EnableEvents = True
    End If
  End If
End Sub

Two Important items:

1 - You will note the Application.EnableEvents instructions. These are required because the code is changing the sheet by clearing the data from the cell. This change would cause the code to fire again (and again and again and again) so the Events are disabled to allow the code to make the change and then re-enabled so the code only fires when a user makes a change.

The key point to remember here is that if the code disables Events and you stop the code (or it crashes) before Events are re-enabled, the code will not fire until you re-enable Events. This can be done by simply running a quick macro such as this:

Sub Events_Enabled()
  Application.EnableEvents = True
End Sub

2 - As written, the code clears any user entered data if the cell is not Red. However, it does not prevent the user from manually choosing a fill color of red and then entering data. Once again, since I don't know what protecction schemes you are using (if any) so that's about all I can offer at this point.

With some more detail, the code can obviously be refined. The only thing that I ask is that you don't following this pattern:

"I need some code to do this."
"Great, that works. Now can make it do this?
"Cool! One more thing. I want it to do this.

Often, when you try to keep is simple at the beginning and then add more and more requirements, the code ends up getting very messy and inefficient. Sometimes it even needs to be completely rewritten from the start. That is not fun for those of us writing the code. The more details you provide right upfront, the better.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#11
July 30, 2015 at 19:38:57
Hey Derby, I'm sorry I didn't check back sooner, This is awesome. I haven't tested it yet but the information from what I can learn from far outweighs what you did for me. Thank you so much. I will post back once I have a minute to get into it.
I didn't give the entire code at first mainly not to take up too much time of yours, But I understand that if someone would keep asking additional questions to eventually lead to what they originally wanted, it would be a greater waste. I have been all over the internet trying to learn VBA by looking at others code and seeing what it does. Kind of a reverse engineering type thing. I appreciate all that you have helped me out with this. Thank you

message edited by itchiTrigger


Report •

#12
July 30, 2015 at 21:11:17
If you want to reverse engineer some code, consider using the debugging techniques in this How-To.

http://www.computing.net/howtos/sho...

That is basically how I learned to write VBA. These debugging techniques will not only help you fix/write your own code, but they're a great way to slowly work your way through code that you find on the web as a means to understand what it is doing.

In addition, don't forget to add comments to any code that you write. A wise man once said:

"The code tells you how, the comments tell you why."

If I didn't add comments to my code, whether it was written for my own use or for other people, I'd have a hard time coming back a few years (or even months!) later and figuring out what I was thinking when I wrote it.

Comments remind you of why you are doing what you are doing within the code, making it easier to adapt the code to meet new requirements.

Have fun!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question