Change fill color depending upon the yes or no dropdown options. [Closed]

Report
Posts
4
Registration date
Thursday December 24, 2020
Status
Member
Last seen
December 28, 2020
-
Posts
1285
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
April 10, 2021
-
Hi,
My question I want to create a vba code for yes or no
If yes- If my columns C-N are blank, I want it to be highlighted by some color and after writing something on to it it should go back to normal.
If no-- "Not Applicable should be autopopulated in all the columns

It would be really helpful if I get help asap.

9 replies

Posts
1285
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
April 10, 2021
224
Hello Ajo,

Here is an amendment. Just replace the other code with this:-

Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)

        If Intersect(Target, Columns("B:N")) Is Nothing Then Exit Sub
        If Target.Count > 1 Then Exit Sub
        x = Target.Row
        
        If Target.Value = "Yes" And WorksheetFunction.CountA(Range("C" & x).Resize(, 12)) = 0 Then
                Range("C" & x).Resize(, 12).Interior.ColorIndex = 6
        ElseIf Target.Value = "No" Then
                Range("C" & x).Resize(, 12).Value = "N/A"
        End If
        If Target.Column <> 2 Then
                Target.Interior.ColorIndex = xlNone
        End If
        
End Sub


Cheerio,
vcoolio.
1
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 2942 users have said thank you to us this month

Posts
1285
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
April 10, 2021
224
Hello Ajo,

Just a couple of questions first.

1) In what column do you intend to place the "Yes" or "No" drop down criteria?
2) Are you using columns A:B?

Cheerio,
vcoolio.
I want to place yes or no in whole column B. And depending on the that yes or no I want other columns say from column D-N to be changed.
Posts
1285
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
April 10, 2021
224 > ajo_5895
Hello Ajo,

I'm assuming that you are not using Column A at all. Try the following code placed into the worksheet module:-

Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)

        If Intersect(Target, Columns("B:N")) Is Nothing Then Exit Sub
        If Target.Count > 1 Then Exit Sub
        x = Target.Row
        
        If Target.Value = "Yes" And WorksheetFunction.CountA(Range("C" & x).Resize(, 12)) = 0 Then
                Range("C" & x).Resize(, 12).Interior.ColorIndex = 6
        ElseIf Target.Value = "Yes" Or WorksheetFunction.CountA(Range("C" & x).Resize(, 12)) > 0 Then
                Range("C" & x).Resize(, 12).Interior.ColorIndex = xlNone
        ElseIf Target.Value = "No" Then
                Range("C" & x).Resize(, 12).Value = "N/A"
        End If
        
End Sub


Please note that I have not tested the above code.

To implement this code:-
- Right click on the worksheet tab which has the data you wish to manipulate.
- Select "View Code" from the menu that appears.
- In the big white code field that then appears, paste the above code.

If you have a Yes/No drop down validation list in each cell of Column B, then, once you select an option, the code will immediately execute.......no buttons required.
If you select "Yes" and Columns C:N are blank then the row (C:N) will high-light in yellow and if you later add values in the same row, the yellow high-light will disappear.
If you select "No", the row will show N/A (for Not Applicable).

I hope that this helps.

Cheerio,
vcoolio.
Hi Vcoolio,
Whenever I try to run that code.. I am not able to see anything inside the macros list. Could you please help me with this.
Posts
1285
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
April 10, 2021
224
Hello Ajo,

The code is an event code and, as such, will not appear in your macro list.
If you have followed my instructions on how to implement the code (post #4), you will see that it is in the sheet module.
To see it, open the VB Editor (Alt+F11) then, over to the left in the Project Explorer, double click on the sheet into which you have placed the code (this should be your main data sheet). You will then see the code.
As I have already said, this is an event code and does not require a button for it to work. You simply make your Yes or No selection and it will execute immediately.
As per your opening post request, if you select "Yes", the row from Columns B:N will high-light yellow. If you then type something else into any of the Columns B:N in an already high-lighted row(and then click away or Tab or move to another cell), the row colour will immediately return to normal. Hence, an event has to happen in a cell for the code to execute.

I hope that this helps.

Cheerio,
vcoolio.
Posts
4
Registration date
Thursday December 24, 2020
Status
Member
Last seen
December 28, 2020

Hi VCoolio,
I followed your procedure and tried changing to yes or no, it is still not working.
Posts
1285
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
April 10, 2021
224
Hello Ajo,

The code is based on your explanations of what you are wanting to achieve and works perfectly in a simulation that I have created.
However, I obviously have no idea how your worksheet/workbook is set out and cannot assume on how it is structured.
The simple thing to do is for you to upload a copy of your workbook to a free file sharing site such as WeTransfer or Drop Box so that we can see exactly what you are trying to achieve. Make sure that the sample workbook is an exact replica of your actual workbook and if your data is sensitive then please use dummy data.
When you have uploaded a sample to a free file sharing site, post the link to your file back here.

Cheerio,
vcoolio.
Posts
4
Registration date
Thursday December 24, 2020
Status
Member
Last seen
December 28, 2020

https://wetransfer.com/downloads/5d9ec76159ddf557d8a035cde6ffd5e420201228122242/74e413


Hello Vcoolio,
Sharing this dummy file but original one has so many merged cells and all of the columns actions depend on each other.
Hope to receive response on this ASAP.

Thanks,
Anuja
Posts
1285
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
April 10, 2021
224
Hello Ajo,

That file sample is not very helpful as you have only a row of headings. I asked for an exact replica of your actual workbook.However, based on the sample, following is the link to your file with the code implemented:-

https://wetransfer.com/downloads/c9cc1c39e32fc10fd1edcf1b54d97fc120201228125248/6289ea

I've placed a few data validation drop downs in Column B with the Yes/No options. Select either and you'll see that the code works just fine in your sample.
To see the code, right click on the Sheet1 tab and select "View Code" from the menu that appears.

If your worksheet has any merged cells, then you must unmerge them. Merged cells create havoc with codes and should be avoided altogether. You can format worksheets easily without using merged cells.

Cheerio,
vcoolio.
Posts
4
Registration date
Thursday December 24, 2020
Status
Member
Last seen
December 28, 2020

Hi Vcoolio
Thanks a lot for solving this.. there one more small issue which is if I try to write something in one cell and then press enter then whole row is getting back to normal.
What I want is cell wise color should change.

Thanks,
Anuja
Posts
1285
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
April 10, 2021
224
Check post #11

Cheerio,
vcoolio.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!