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

Question

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.

vcoolio · Answer

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.

ajo_5895 · Answer

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.

vcoolio · Answer

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.

ajo_5895 · Answer

Hi VCoolio,
 I followed your procedure and tried changing to yes or no, it is still not working.

vcoolio · Answer

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.

vcoolio · Answer

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.

vcoolio · Answer

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.

ajo_5895 · Answer

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

vcoolio · Answer

Check post #11

Cheerio,
vcoolio.

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

9 responses

Similar discussions

Subscribe To Our Newsletter!