Colour Formatting Rows By Validation List

Feb 12, 2008

My Objective: Simplified
Assuming a range A1:G15
A Validation list in column H

The Validation List options are: Blue,Pink,Green,Black,Grey,Yellow,Orange and None or '

how I create a Case Select code to change, the row range dependant on the option selected in the validation list.....or most efficient code to achieve my objective.

Or the most efficient code method to achieve the outcome I am seeking.

What I can't figure out 1:
How to incorporate the validation list selection to recognise the colour option selected.

What I can't figure out 2:
I want to be able to format a row any of colours in the list, so if it is black, would I need to clear the black format before changing it to say Orange, or is it possible to go straight from black to Orange?

What I have tried:
I am aware of the limitation of three formats using "Conditional Formatting" so want to expand the colour formatting possbilities using code, by selecting choice of format colour from a validation list.

I did a macro recording, selecting the appropriate option within the Validation List, and then formatting a row range....I experimented with the resulting code to no success, although I have a copy of VBA and Macros for MS Excel....theres a lot to think about....and I can't quite grasp what's required.

Code I have used in the passed for a similar but different worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Err_Handler

View 9 Replies


ADVERTISEMENT

Colour Rows Based On List Colours (No Conditional Formatting)

Oct 3, 2012

As far as I can tell Conditional Formatting will not work for this problem.

What I need now is some code to colour the rows in the data sheet ("Standards_Data"), so some other code (which is working fine) can then colour data points on a multiple graphs (which will be on the "Graphs" sheet). I've played around with as much code as I can find but nothing has the flexibility that I need or doesn't seem to work at all.

The idea is that users can adjust the colours using the Column A on the "Graphs" sheet to best highlight certain things. Also this list could then be edited as old Standard ID's become redundant and new ones come on line.

So in a nutshell: Colour rows in "Standard_Data" based on value of Standard ID (Column 4) according to the corresponding colour found in the list in the "Graphs" sheet.

I've attached a trimmed down version of the workbook.

View 4 Replies View Related

Created Validation Drop-down List - Can I Make The Row Now Change To The Colour That Relates To The Selection From The List

Jan 13, 2009

I am trying to create a fairly simple spreadsheet with about 8 columns and about 400 rows. One of the columns features a drop-down list with about 8 or 9 different options. Dependant on which option is selected, i would like the entire row to change colour with that option.

For example:
FAILED - whole row changes red
SUCCESSFUL - row has no fill
Tested - row changes to orange

etc.

Is this possible within Excel 2003?

View 9 Replies View Related

Conditional Formatting To Change Colour Of Rows

Aug 13, 2009

I want to use conditional formatting to change the colour of the entire row when the date on A1 is any date in September (01/09/09 to 30/09/09).

View 10 Replies View Related

Apply Conditional Formatting If The Cell Contains Validation List?

Jul 13, 2013

I have a table. I want to apply conditional formatting to the entire table so that wherever a cell contains a dropdown list (validation list) the cell is formatted with a different colour.

View 3 Replies View Related

Validation List With Non-consecutive Rows

May 23, 2007

What I need specifically is a situation where you choose the Month/Year in a drop down menu in say A1, and then cells B1-Bx display the dates of each day of the month. I’ve been reading tutorials at breakneck speed, and will probably continue to do so.

View 2 Replies View Related

Using Data Validation To Create List Dependent On Multiple Rows?

Jul 28, 2014

I have three individual lists and I am using a formula like this for each of them =OFFSET(Table1,MATCH(F15,Table1,0)-1,1,COUNTIF(Table1,F15),1)

for my final cell I need to create another data validation list which is depenant on the values selected in the previous three lists.. how I would alter the formula to allow me to do that? I tried using and after the match to match all three tables but it never worked

View 12 Replies View Related

Macro - Hide Rows Based On Data Validation List?

Dec 30, 2011

I have a data validation cell that has 5 items to be selected, lets say A-E.

I would like a macro that checks and does the following:

A or B selected, hide row 25-34, rows 45- 53

C or D selected, hide row 24-44

E selected, hide rows 34-53

In each case, the macro should check and unhide all rows between 25-53 before doing the above

View 9 Replies View Related

Load Rows Data On Selection Of Data Validation List

May 28, 2014

I want to load the actual data exist in the rows by selection value from the data validation list.

E.g I have two worksheet in one excel file. One has a data activities of persons with their name like two columns i have in which one exist the name of person and second exist the activities which they perform.

On the second sheet, i made a data validation list of all the person names

Now my requirement is, when i select a person name from the list, load all the data from the 1st sheet to second sheet. Is this possible without VB code, because I want to share it on the Google sheet with my boss, where VB sheet is not supposed to work.

View 13 Replies View Related

Conditional Formatting With Colour

Jul 17, 2009

Im trying to create a stock database based on a colour traffic light system. I have 2 set columns, the first is 'recommended stock' the second is 'actual stock'

When you paste in your requirements into the 3rd column it subtracts it from the acutal stock which tells you how much your recommended stock is short by.

What ive got as well is any stock above 1+ shows up in green (this needs changing - see below), anything below 0 shows up in red. What im struggling with is i want a rule that reads the recommended stock level, then flags it up yellow when the actual stock levels drop to 10% of full stock.

how to use the conditional formatting if that is the way.

View 9 Replies View Related

Conditional Formatting - No Data No Colour

Nov 4, 2008

On this particular formula but my colleagues have informed me that they require cells with no dates in to have no colour fill. Firstly, I have searched and tried myself with no success on stopping the below formulas from filling empty cells with red, they are as below;

Code below for 11 months after entered date:

View 2 Replies View Related

Consitional Formatting/ Cell Colour

May 20, 2009

I have an excell problem. I would like to use conditional formating.

However I need to compare four cells for example:

If Cell A1 = Cell A2 and Cell A4 = Cell A5 Then

Colour Cell A5

View 3 Replies View Related

Conditional Formatting - Colour A Whole Row Of Data

Jun 8, 2009

I am trying to colour a whole row of data (in columns A through to AA) based on the numeric entry in column AB. The numeric entry is either 1, 2 or 3. I reckoned I needed to use the IF function, but as writing formula is not my strong point I am struggling to enter the correct data! The first row of data I wish to use is 3. I have tried the following but to no avail: =IF($AB3,1). =IF($AB3=1). =IF($AB3="1").

View 5 Replies View Related

Conditional Formatting: Change Colour

Feb 16, 2010

In cell A1 I have revenue projected & B1 I have actual revenue if the date in B1 is less than the date in A1, I would like B1 to change colour.

View 5 Replies View Related

Conditional Formatting Cannot Keep Original Colour?

May 26, 2014

I am having problems with conditional formatting,on sheet1 ,if column "n" is yes then it is coloured red,when I search database say f124 I would like all records for f124 to appear and keep column "n" red or even better the whole row red,it seems just to locate all records with "n" =no and all orange /brown colour

View 6 Replies View Related

Conditional Formatting To Colour Cells

Nov 7, 2008

I'm designing a holiday chart in work, and am using conditional formatting to colour cells. The problem is I have more than 3 conditions. Consequently I need to use a macro.

View 5 Replies View Related

Formatting Part Of A Cell (colour)

May 13, 2012

I have a range where there text is listed like this

A........B........C
= Blue
+ Green
= Orange

This is the text to search for - and the colours I would like the text to be.

Is it possible to search (D5...D17) for these and set just those characters to the required colours. So that....

10 If Point 0|123 4.5000 then GOTO 50

would become

10 If Point 0|123 4.5000 then GOTO 50

View 1 Replies View Related

Conditional Formatting: Change The Colour..

Oct 18, 2007

=$CS18=INDIRECT("Client_Options!"&ADDRESS($I18,97))

I want to say when CS2= same value as CS2 on the client options sheet (on the row specified in I2 sheet 1) change the colour.

How should this be tweaked to do that? I'm not getting it!

=$CS2=INDIRECT("Client_Options!"&ADDRESS($I2,COLUMN()))

View 2 Replies View Related

Conditional Formatting: Set The Colour Of A Cell

Jul 11, 2006

I am using conditional formatting to set the colour of a cell. I now want to change the colour of the adjoing cell to the same as the conditional format cell. CF doesn't have this as an option

View 6 Replies View Related

Colour Rows With More Than One Colour

Nov 24, 2011

I'm creating a worksheet which tracks pupils progress in our school. down the left there is a column to put in the level they came into our school on - eg 1C or 3B etc.. to the right of the level is a colour coded row which shows Pink, Yellow and Green - if the pupil makes it into the green part of their level by the time they leave then they are on target.

Basically I have a column of cells which use a data validation list which is linked to another sheet with a list of levels. What I want to happen is when I select the level - it fills the row to the right with the correct colours.. I've attached a picture to show you what i'm trying to do.

Reason being is - we might have 6 kids on L3, and 1 on L1 so the number of rows needed for each level changes - at the moment I'm having to copy and paste - which although is easy enough, it is a bit tedious when you have a whole bunch of pupils to put on the grid.

I can't find the option in imageshack to use a thumbnail

View 6 Replies View Related

Cell To Meet Same Colour As Another / Conditional Formatting

May 20, 2014

I need a formula which will, alike conditional formatting, change the colour of a cell based on date or another cell.

For Example, If I was to enter a value of today's date it would be green up to a 3 month period, after 3 months it turns to amber and after 6 months red.

I can do it with conditional formatting on the date function when converted to days (see attached). Is there any way I can make the date updated column automatically match the date difference colour with a formula?

Prices and Costs - Carbon Steel.xlsx

View 7 Replies View Related

Change Colour In A Cell - Conditional Formatting

Dec 4, 2008

I want to change the colour in a cell depending on the sum of two other cells.
ie
A3=1,B3=1
A4=2,B4=1
A5=1,B5=0
A6=2,B6=4

So i want if
A3+B3>2 THEN C3 TO HAVE GREEN COLOUR
A4+B4>2 THEN C4 TO HAVE GREEN COLOUR
A5+B5>2 THEN C5 TO HAVE GREEN COLOUR
A6+B6>2 THEN C6 TO HAVE GREEN COLOUR

SO IN THE END ONLY C4 AND C6 WILL HAVE GREEN COLOUR.

Is there any formula to do so with conditional formatting?

View 9 Replies View Related

Conditional Formatting And Change Its Background Colour

Jun 30, 2009

I'm trying to set a conditional formatting to do the following:

If A1 is greater than 1, A2 will change its background colour to green. So the format needs to be in A2.

Basically in A1 is a page number that we have reconciled our bank statement to (labelled as "64" or "65" and so on), A2 has the end of statement amount.

Every month end we do a bank rec, the green cell is a marker to show what point we reconciled up to previous month.

View 2 Replies View Related

Conditional Formatting: Change The Cell Colour

Aug 21, 2008

I am trying to setup a monthly calendar.
What I want to do is have it so that if you type "apples I want some", the cell changes colour to green, and also the apples doesnt appear in the cell. So when you press enter, all you will see is a green cell that says "I want some"

I was wondering if there was a way in VB within the worksheet to make this happen?

View 9 Replies View Related

Colour Alternate Lines With Conditional Formatting

Jul 28, 2006

change the colours of alternate rows in order to make reading line by line easier. I know that I have used this before as a Conditional action ie:- if(mod...... etc

As always, the program I used it in has 'disappeared' so I can't check back with that.

View 5 Replies View Related

Conditional Formatting Query: Changes Colour When Certain Text Is Entered

Sep 10, 2009

I have a column of cells that changes colour when certain text is entered into them, ie "TIME" will make the fill colour brown. However, when other text is entered into the cell alongside "TIME", the conditional formatting doesn't change the colour of the cell. How do i get around this so it picks up on the "TIME" text even when other text exists alongside it?

View 2 Replies View Related

Conditional Formatting Not Changing Font Colour As Required

Oct 3, 2009

If a cell cntent includes a symbol [as an arrow, for instance] beside the text,the font colour does not change as required when the condition is met.

View 2 Replies View Related

Conditional Formatting - One Cell Green Rest No Colour?

Jan 31, 2013

I have five columns D3 to H3 all with different amounts/currency value. I'm looking to get a formula or use conditional formatting to scan those five cells and highlight one of them green when Excel has determined which one is the cheapest out of the five.

View 14 Replies View Related

Use Conditional Formatting If 360 Cells Automatically Changing Colour Red

Sep 10, 2013

I have figure A1 column which total amount accounting at cells A31. I need figure 360 as a benchmark calculation perfectly been done. In this calculation 360 well off inside cells A18 although actual number is 363. If we use Conditional Formatting when had enough total in cells the achieve 360, cells colour will be transformed to red. What formula which need I used to change no matter cells on A1 to A30 if sufficient study 360 automatically cells changing colour to red

View 5 Replies View Related

Conditional Formatting Colour Change Based On Dates?

Jan 8, 2012

I have a simple due date spread sheet for tasks that need completing at work. I am able to make the 'Due date' cell turn red when it is equal to or less than todays date, using conditional formatting. No problem.

I also have a 'Date closed' cell which is populated with a date when the task is complete.

What I am struggling to do however is change the red 'Due Date' cell to Grey once the 'Date Closed' cell is populated with a date. In fact I would like the whole row to be greyed out once the 'Date Closed' cell is populated.

I also have an 'Open / Closed' cell, which tells me if a task is open or closed, being chosen from a drop down I installed. Could this be used as an alternative to the 'Date Closed' cell. ie If someone selects 'Closed' in cell A4 the whole A row goes grey.

View 4 Replies View Related







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