Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 1
Default if then, change value of a cell range

is this possible? or some other syntax to make this work

=IF(A6="WE",A11:A24=" ","")

if A6 = “WE” then a ceartain column range is cleared of its contents, if not content stays as is

help is greatly approciated.

thanks.
  #2   Report Post  
Junior Member
 
Posts: 18
Default

Quote:
Originally Posted by jo8502 View Post
is this possible? or some other syntax to make this work

=IF(A6="WE",A11:A24=" ","")

if A6 = “WE” then a ceartain column range is cleared of its contents, if not content stays as is

help is greatly approciated.

thanks.
Hi,

if it is possible for you to use macros in your workbook, then this should
solve your broblem.

1 ) Open the workbook ( or a copy of it ) that you are intend to modify

2 ) Press ALT + F11

3 ) at left part of window, doubleclick that worksheet that contains the
cells A6 and A11:A24 that you mentioned

4 ) paste the following code to the right part of VBE-window


Private Sub Worksheet_Change(ByVal Target As Range)

Dim SomeText As String

SomeText = UCase(Range("A6").Value)

If (SomeText) = "WE" Then
Range("A11:A24").ClearContents
End If

End Sub



5) save the file

6) shut down the Microsoft Visual Basic - window

7) you dont have to do anything else to get this work


Now, always when something changes at this particular worksheet, for example
when a value of any cell changes this macro will execute. What it then does ?

It checks if the value cell A6 is WE, We, wE or we and if that is the case
then clears contents from A11:A24. Else it wont do anything.


I don't know how your goal could be possible to accomplish by ordinary worksheetformulas.
Thats because a cell can contain text, date, formula etc. and if a cell at area
A11:A24 contains for example a timevalue, it can't at same time contain some permanent formula.



This macro does the following things :

Defines a variable named SomeText, which can contain any string-value

Takes value to SomeText-variable from cell A6

Checks if the value is some of these ( We, we, wE or WE )

if it is the contents from cells A11:A24 will be cleared immediately

if the content of cell A6 is something else nothing happens, only
the execution of macro will be stopped.


It is important that you save the code to the right place. It wont work
from module part of project.

***
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula that does not change the cell range FC Excel Discussion (Misc queries) 5 June 8th 07 07:53 PM
change pattern in a range of cells if a given cell = pilgrim New Users to Excel 2 May 9th 07 06:40 PM
How do I change cell color based upon data range within the cell? Chris Sanders Excel Worksheet Functions 1 March 6th 06 09:59 PM
How do I change a cell range with a reference cell? Danneskjold Excel Discussion (Misc queries) 2 August 11th 05 07:37 PM
Look for change next blank cell in Range Nigel Bennett Excel Worksheet Functions 1 March 13th 05 10:45 PM


All times are GMT +1. The time now is 03:34 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"