Cell Format Dependant On Rows

Mar 13, 2007

I have


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
With Target
Select Case .Value

Case "(None)": .Interior.ColorIndex = Null
Case "One": .Interior.ColorIndex = 38
Case "Two": .Interior.ColorIndex = 18
Case "Three": .Interior.ColorIndex = 35
Case Else: .Interior.ColorIndex = xlNone
End Select
End With
End If

End Sub

My problem is that columns A to H are yellow(36 I think) and are merged cells.
And Cells AW2 to BD2 are also yellow. The cells inbetween are white.

At the moment when the Cell value is "(None)" the cells turn white, I dont want this. I would like them to go to default.

View 9 Replies


ADVERTISEMENT

Inputting Dependant On Cell

Oct 12, 2008

on sheet1 which is called working sheet and cell d25 is asking what type of goods been sold

i have several different sorts"

ie
s25
s28
s50
s69
s70
s82
abd
t70
t140

i want the spreadsheet to look up somewhere on a sheet called delivery all the components that could be used for that type

so i guess i need to name define?

ie if s25 is selected

on delivery sheet in a20 it would then list all the components underneath each other.

View 14 Replies View Related

IF Formula That References A Cell And Returns A Different Result Dependant On The Number In The Cell Being Referenced

Oct 2, 2009

I'm trying to do a formula that references a cell and returns a different result dependant on the number in the cell being referenced.

For example I've said if A1 has a 3 in it then put the word TEST as the result, plus if it has a 4 put the word RESULT.

What I wrote as my formula is as follows-

=IF(A1=3,"TEST")+IF(A1=4,"RESULT")

It works fine when I only use one result but goes wrong when I add two. If I change the words I want to show to numbers it comes up fine but with words it just returns a Value error.

View 2 Replies View Related

Automatically Shade Cell Dependant Upon Text In Aother Cell

Jul 13, 2009

Im writing a spreadsheet that will act as a hotel room booking diary. There will be 52 sheets (representing each week of the year) and each sheet will therefore represent one week

Each sheet will look at operate the same. Running horizontally will be the days of the week and running vertically will be each of the bedrooms. There are 22 bedrooms in total and each room will have 12 cells allotted to it for each day - lets call this a 'block' and presume that the first block runs from C10 to c21. The final of these cells C21 will have a validation and the user can select 'PAID' 'TO PAY' or 'INVOICED'

If the user selects 'PAID' in C21 I want cells C10 through to C21 to shade GREEN
If the user selects 'TO PAY' in C21 I want cells C10 through to C21 to shade RED
If the user selects 'INVOICE' in C21 I want cells C10 through to C21 to shade YELLOW

View 3 Replies View Related

Formula For Obtaining Value A Dependant On Cell X

Jan 16, 2010

so basically, im trying to find a way to get cell G3 to have Value X dependant on value from Cell G2. e.g. if cell g2 is 8 and 9, g3=10 if g2 is 11, g3=20 and so on. i dont mind having to add a preset table of values if we need, but it would be set on sheet 2.

EDIT
Value from cell G2 will always be between 4 and 20, set by the user. what i want is that each of those values gives G3 a pre-planned value.
if g2 has value 4. g3 value would be -5
if g2 has value 5. g3 value would be 0
if g2 has value 6. g3 value would be 5
if g2 has value 7. g3 value would be 5
if g2 has value 8. g3 value would be 10
if g2 has value 9. g3 value would be 10
if g2 has value 10. g3 value would be 15
if g2 has value 11. g3 value would be 20
if g2 has value 12. g3 value would be 20
if g2 has value 13. g3 value would be 25
and so on until it reaches g2=20

i was thinking of setting a table on sheet 2 for the g3 values, and get an IF function to do something like IF g2 = a then x, if g2 = b then y etc etc. im just not sure how it would work, unless an INDEX function would work better?

View 4 Replies View Related

Copy Picture Dependant On Cell

Jan 9, 2007

I am trying to make an Excel sheet with a frontpage sheet and a data sheet. In the data sheet I have several columns with data and then in the end a cloumn with pictures. On the front page sheet I can easily with VLOOKUP formulas transfer the desired data from the data sheet to the front page sheet but I cannot seem to figure out how to copy the picture. When I try to copy the cell with the picture in it, the formula only copies the cell value, which is "0" because the only thing which is in the cell is the picture. how to copy a given picture from one sheet to another based on the information in another cell like a "Vlookup" formula?

View 3 Replies View Related

Image Dependant On Cell Content

Aug 31, 2007

i have a column with numbers in, in numerical order, however some cells are null. ie.
1
2

3


4
..
etc. I need to put paste images from a folder next to these numbers. (1.bmp next to '1' etc) and i need to leave the empty cells in. This code is sort of right..

Sub aids()
Dim strPath As String
Dim strFile As String
Dim lngRow As Long
Dim objPic As Object
Dim sngMaxWidth As Single
On Error Resume Next
lngRow = 2
strPath = "C:images"
strFile = Dir(strPath & "*.bmp")
With ActiveSheet
Do While strFile <> ""..........

View 7 Replies View Related

Color Row Dependant On Cell Entries

Jun 3, 2008

I have a spreadsheet where I would like the rows to change colour dependant on value input to certain cells, i.e. if a any value other than a zero put into a cell it changes to green, if a zero put into a cell it changes to grey, if a zero put in but a value put in another cell it changes to another colour, if values already in are taken out of other cells, it changes to yellow.

I've looked on FAQ and there are a lot of similar things but not quire wha I'm after and even thn I dont know how to use VBA etc to achieve.

View 9 Replies View Related

Make Text Dependant On Cell - Bold

Feb 18, 2010

i have some code where i need to make the text bold but not sure how to go about it
the formula is

View 3 Replies View Related

Save A File Dependant On Cell Reference

May 21, 2007

i have a order form that is customer dependant. the cust name on the form is manually entered by the user within a msg box. i have specific cusotmer folders on a shared machines C drive where i would like to save these excel docs to.

i understand that the cust name must match the name of the file and there needs to be a way to save those that do not have specific folder matching what was entered as the customer name.

the overall goal is to have the file name saved as CustomerNameDeliveryDateInvoiceNumber.xls where CustName is from cell "M3", delivery date is "Q7" and InvoiceNumber is "Q1".

Sub OrderFormSave()

Dim strCustFileName As String

savefile = "\SrSharedDocsCSPSharedFILESCustomerOrderForms & strCustFileName & "

ActiveWorkbook.SaveCopyAs savefile

End Sub

View 9 Replies View Related

VBA Restriction To Cell Input Dependant On Other Cells

Oct 2, 2007

I have a sheet where I want to not enable someone to enter data in a cell unless there is data entered in a range of other cells.

Range A1:A5 compulsory data to be entered
Range A6:A20 No data can be entered can be entered unless A1:A5 is filled in

Does someone have some code that can enable me to do this?

View 9 Replies View Related

Cell To Respond Either Or Dependant On Values Of 2 Cells

Mar 3, 2008

I am trying to get a cell to respond either or dependant on values of 2 cells.

If A1 = 13
B1 = 0
C1 = 0

I would like D1 to return A1, but

If A1 = 13
B1 = 1
C1 = 0

or

A1 = 13
B1 = 0
C1 = 1

I would like D1 to return the sum A1 + B1 + C1

View 9 Replies View Related

Change Cell Colour Dependant On Text

Jul 20, 2006

how hard would it be to chane the background colour of a cell when a certain word is entered into it? so someone would type 'james' and press enter then the cell would turn red....would only be in a set cell range..

View 9 Replies View Related

Variable Locked Cells Dependant On Cell Change

Jan 12, 2010

I'm trying to make a spreadsheet more secure to prevent unintentional changes.

What i'm looking to do is to lock all cells so no changescan be made unless the user clicks column A.

Column A should remain unlocked and when the user clicks it i would like the entire row unlocked for editing (but only after column A is clicked)

I will run a macro to lock the entire sheet again once the sheet closes.

View 2 Replies View Related

Propagating Text Boxes Dependant On Cell Relationship

Oct 8, 2006

I have been working for three straight days (and nights!) to accomplish something that is very simple in other languages, but I can't make it work in VB

Column X has, with spaces between 4 possible contents:
3Q
FM
1Q
NM

St1 St3
3Q today
tomorrow
-2
-3
-4
-5
yesterday
FM today
tomorrow
-2
-3
-4
-5
-6
yesterday
1Q today
tomorrrow
-2
-3
-4
-5
yesterday
NM today
tomorrrow
-2
-3
-4
-5
yesterday
3Q today
tomorrow
-2

and so on for 1500 rows.

I can write this in Lotus in about 2 minutes, but VB has me stumped. I hope someone can put me on the corrrect path.

View 8 Replies View Related

Unlock Cell/s Dependant Upon Entry In Another Cell

Jan 3, 2010

I've never dealt with vba before and I'm not even sure if that's correct. I wish to lock a worksheet with two cells left unlocked, when an entry is made in one or other of these two cells I need other cells to become unlocked.

View 2 Replies View Related

Macro That Saves Into Specific Folder Dependant On Cell Values

Oct 20, 2008

i have this macro that saves into specific folder dependant on cell values and it works spot on with the exception that my po numbers are like st010,st011,st012 etc etc but when it saves it doesnt have the st in front. ive tried numerous ways but to no avail

View 5 Replies View Related

Excel 2003 :: Insert Rows Then Format Cell In New Row?

Apr 11, 2013

Excel 2003 / WinXP. I have a list object with 5 columns, A - E, and all cells have a border round them. I need a macro which will:

1. insert a row below the current row (whatever cell happens to be selected)

2. then remove the left hand border of the cell in column B in the new row,

3. then select the adjacent cell in column C in the new row ready for data entry.

Obviously I cannot specify any absolute addresses in the code, and this is where all my attempts to record a suitable macro fall down.

View 7 Replies View Related

Macro - Format Rows Based On Cell Contents

Aug 18, 2009

I have a spreadsheet that has a subtotal row after each data grouping. I need help with a macro that will go to column C, look for any cells with the word "Subtotals" in it, and format that row based on another macro I already have set up. I've tried about 50 different types of macros that I found out on the web, but none have worked.

View 9 Replies View Related

Excel 2007 :: Format Rows Based On 1st Cell Populated?

Nov 16, 2012

I am using Excel 2007

I am trying to conditionally format rows based on the first cell in each row. I had this working in a previous worksheet and have gone through several examples based on answers in other threads but do not for the life of me know what I am doing wrong. I am using the conditional formating rules on the ribbon menu.

I have a range A3-W100 pre-formatted with generic data and formulae. Column A has no data. I want the text to change color for that row if the cell in column A is populated. The background and text are set to black to appear unpopulated.

When an item number is entered in column A3, the text color in row 3 changes to white. I can get this to work on one row but if I extend it through the range, all rows change. I want them only to change if the respective A cell is populated.

I am using
Formula is =A3>""
Format is Yellow,
Applies to =$A$3:$V$100
Stop if True is unchecked

All I can get is column B to change color.

View 6 Replies View Related

VBA Conditional Format Range Of Cells Based Upon Criteria In One Cell / Repeat For All Rows

Apr 3, 2014

Code is trying to

1. Where there is data in column B
2. Goto first row of data
2. If column J = "Closed"
3. then select Cells from column B to J in that row and colour these cells in red
4. If column J= "Open"
5. the select Celss from column B to J in that row and colour these cells in grey
4. Repeat for next row until you get to cell in column B where there is no data.

Recieve run time error 1004: Method 'RAnge of object_GLobal failed

===============================================================
Sub Colourclosed()
Sheets("Risks").Select
Dim LastRow As Long
Dim i As Long
LastRow = Range("B" & Rows.Count).End(xlUp).Row
For i = 8 To LastRow
If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select
Selection.Interior.ColorIndex = 3

[code].....

Note: I've managed to do it for one cell ie for Column B by replacing "If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select with "If Range("J" & i).Value = "Closed" Then Range("B" & i).Select" This works but only colours in cell in column B, how do i do this so it colours range of cells

View 4 Replies View Related

Copy & Append To Report, Rows From Multiple Sheets If Any Cell Meets Format Color

May 1, 2008

I've seen a few threads on here about this issue but none of them do quite what I am looking for. I'd like for a single page "report" to be created when a user presses a button (which runs a macro, of course) The macro should be able to run through certain named sheets (even if hidden) and if a cell in any row is red within a sheet then the entire row or rows that meet the criteria should be copied and pasted into the Report sheet.

On the report sheet, for each sheet that has had rows that were copied, I'd like to have the name of the sheet as the header above the pasted rows so that the user knows which sheet the data came from. Any sheet that doesn't have red cells would be excluded from the report. I've attached a sample file but had to limit the number of sheets because of Orgrid's file size limit. Hopefully, you'll see what I am getting at here.

View 8 Replies View Related

Conditionally Format Each Group Of Rows And Each Column And Color The Cell In Each Column

Nov 27, 2009

I've a worksheet that is 3750 rows of cells from A to DT and contains only numbers with the exception of a header row. The rows are in groups, primarily 4 rows, although there are exceptions - these rows are separated by blank rows. Small example as follows.

0:001:002:003:004:005:006:007:008:009:000000-296567-6-1-400000286567-300-230000715557-16-11-40-18141755226-348405-362203081417192125-893581061800-2443-11124-289326-81318-1105027854331331211318-10216-161235526785691450451115951-17-1882505656683093102671056-191-33-2710000554740971-61-78-34224107-169-128-17090179-85220-55-63-1-162-140-242-235328266319106113-214-64-148205-4108120-142076-167-60-68-6418081102-800000000000-3099695807185-80356138-123194186210677727125-23233092-2942944748055564476-422051-224-34461486649597117-84209-22-251-94

I have been trying to conditionally format each group of rows and each column and color the cell in each column of four (or less) numbers in each group that represents the maximum number.
It was easy enough to come up with the conditional formatting BUT it will take forever to do this manually. I've been told to use a macro but I know nothing of programming. Has anyone seen or heard of a macro that might already be written to perform this onerous task.

View 9 Replies View Related

Change Cell Format Based On Existing Currency Format

Jun 12, 2008

I would like to have a single button that changes a range of cells from the USD to EURO to perhaps CAD symbol. Can this operation be performed, such that if I start in dollars, and I click the button once, it shifts by range to EURO (not formulas...just symbol)...and then if I click the same button again, it goes to CAD, and then back to USD with a third click?

View 5 Replies View Related

Dependant Validation

Aug 3, 2009

Basically what i need to do is create a validation list, the contents of which will be dependant on the entry in another cell. The catch is that the other cell contains an "if" formula.

eg.

A1: 10
B1: =if(A1=10;"A";if(A1=11;"B";if(A1=12;"C";"Invalid Entry"))) ....

View 9 Replies View Related

Sum Color Dependant

Nov 21, 2006

I want to sum a range of cells but exclude any that are in a particular colour (in this case red).

View 3 Replies View Related

Format Cell: Changing The Format Of Sheet2 To Exactly Like The Following Image Below

Jun 27, 2007

I need in changing the format of sheet2 to exactly like the following image below.
*there are some codes in sheet2 which I think is the place to change the format.

View 6 Replies View Related

Return Answer Dependant On /

Apr 5, 2009

in cell d1 i have

hk/q46559

in cell i1 i need to return whatever is before the "/"
in this case hk

and in cell i2 i need to return the number after the q
in this case 46559

View 3 Replies View Related

Multiple Dependant Dropboxes

Jul 8, 2013

I have a worksheet with 4 drop boxes that have the following options in order: 2,28,10,24. My main concern is to make the last drop box depend on the third: so the one with 10 listings will determine which of the 24 will appear according to selection. The other two are not necessarily needed to be dependent although, they will be pulling different data according to the selections. How to make the last two be dependent? The combo box selections will determine the values pulled for four different time frames.

View 4 Replies View Related

Showing Different Results Dependant On Drop Down Box

Sep 19, 2007

I'm creating a spreadsheet to track orders as part of a project, the spreadsheet currently lists all the different options that can be ordered, it then lists all parts of the order, installation & setup etc.

However it is likely that not all of the options will be used.

What I would like to do is create a few drop down boxes for each option, if 'yes' is selected then the order tracking for that option shows below.

Example, the wants 'internet' so 'yes' is selected for internet using the drop down at the top of the page. Further down the page all the order tracking info is shown for internet. If 'no' is selected nothing is shown for interent.

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved