Help with IF - Clearing contents of cells based on value of another sell

lnagel · May 28, 2018

All,

Stuck here and have tried lots of IF variations. I have the following spreadsheet

	A	B	C	D	E	F	G	H
1	[FONT=&quot]NUM[/FONT]	[FONT=&quot]PLAYER[/FONT]	[FONT=&quot]L/R[/FONT]	[FONT=&quot]T/B[/FONT]	[FONT=&quot]GRADE[/FONT]	[FONT=&quot]S/N[/FONT]	[FONT=&quot]COST[/FONT]
2	[FONT=&quot]1[/FONT]	[FONT=&quot]Dusty Rhodes[/FONT]	[FONT=&quot]50/50[/FONT]	[FONT=&quot]65/35[/FONT]	[FONT=&quot]SGC 84[/FONT]	[FONT=&quot]70%[/FONT]	[FONT=&quot]8216088-002[/FONT]	[FONT=&quot]32.07[/FONT]
3	[FONT=&quot]2[/FONT]	*[FONT=&quot]Ted Williams ^HOF[/FONT]*	[FONT=&quot] [/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]NEED[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot] [/FONT]

The Values in Column C are selected from a drop down list
The Values in Column D are selected from a drop down list
The Values in Column E are selected from a drop down list
The Values in Column F are returned using HLookup from a table
The Values in Column G are a manual entry by user
The Values in Column H are a manual entry by user

What I want is a formula that will Clear Columns C,D,G,and H IF and only IF the user Selects "NEED" from the Drop Down List in Column E

Column F automatically clears based on the HLookup value returned

Not sure why I can't figure this out - Thanks in advance for any response

Fluff · May 28, 2018

This cannot be done by formulae, as a formula can only affect the cell it's in.
Are you happy to have a VBA solution?

lnagel · May 28, 2018

OK - thought I was losing my mind for a minute... Yes I can give the VBA thing a go. Note I haven't ever used VBA so I will need the "newbie" instruction set. Thanks

Fluff · May 28, 2018

Ok, try this

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   Application.EnableEvents = False
   If Not Intersect(Target, Range("E:E")) Is Nothing Then
      If LCase(Target.Value) = "need" Then Intersect(Target.EntireRow, Range("C:D, G:H")).ClearContents
   ElseIf Not Intersect(Target, Range("C:D, G:H")) Is Nothing Then
      If LCase(Range("E" & Target.Row)) = "need" Then Target.Value = ""
   End If
   Application.EnableEvents = True
End Sub

To install this code right click on the sheet tab > View Code > Then paste the code into the window that opens up.
You'll need to save the workbook as an .xlsm file.
The code will trigger automatically.

lnagel · May 28, 2018

That was easy - Thanks so much

I think I gotta learn this VBA Stuff now -

Fluff · May 28, 2018

Glad to help & thanks for the feedback

lnagel · May 29, 2018

After getting the Clear Contents routine working - I was thinking that I would like to change the font on Conditionally Formatted "NEED" box as Conditional Format does not allow Font Changes. I have added the code which, I thought, would change the font in the cell to Arial. I do see the Arial Font "flash" really quick and then the cell reverts to the default font. Tryin not to be a pain - if you could take a look and point out the error would be much appreciated.

If Not Intersect(Target, Range("BH:BH")) Is Nothing Then
If LCase(Target.Value) = "need" Then Intersect(Target.EntireRow, Range("BF:BG, BJ:BK")).ClearContents
If LCase(Target.Value) = "need" Then Target.Font.Name = "Arial"
ElseIf Not Intersect(Target, Range("BF:BG, BJ:BK")) Is Nothing Then
If LCase(Range("BH" & Target.Row)) = "need" Then Target.Value = ""
End If

Thanks

Fluff · May 29, 2018

There's nothing wrong with what you've done.
In the VBE put the cursor anywhere on this line

Code:

   If Target.CountLarge > 1 Then Exit Sub

& press F9, the line should then have a brown background.
Change a cell to Need & that line should then be highlighted yellow. You can then step through the code line by line using the F8 key.
Does that show you when the font is changed back to the original?

lnagel · May 30, 2018

It appears that the Conditional formatting was overwriting the VBA Code - I deleted the conditional Formating and wrote the formatting into VBA Code - All working now - Thanks again

Fluff · May 30, 2018

You're welcome

Help with IF - Clearing contents of cells based on value of another sell

lnagel

Board Regular

Excel Facts

Fluff

MrExcel MVP, Moderator

lnagel

Board Regular

Fluff

MrExcel MVP, Moderator

lnagel

Board Regular

Fluff

MrExcel MVP, Moderator

lnagel

Board Regular

Fluff

MrExcel MVP, Moderator

lnagel

Board Regular

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Help with IF - Clearing contents of cells based on value of another sell

Board Regular

Excel Facts

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock