Looking Up Column Header Value Based On Color And Position In Row

Dec 12, 2009

I am trying to create a schedule using colors. The colors will indicate the start time, break time, lunch time, break time (second break) and end time. see attached document.

Thank you for attempting to solve this puzzle using formulas and or VBA code. I am trying to automate the schedule so the numerical times automatically appear when I "paint" the schedule.

Macro: Changing Color Of Coloumn Header Based On Rows Color

Jan 15, 2009

I wrote a macro to color the cell values in the rows based on their average value. For eg if the cell value is less than 0.2 Avg, they should be red color,if value is between 0.2 and 0.5 it should be yellow. This part is working fine

Now based on the color of the rows cells , need to write a macro for the header one. Logic is Coloum header should be in red colour, if in one or more number of rows cells are red. same with yellow ones. Could you please help me out in solving this with logic.

Return Row Heading By Column Header And Specific Texted Cell Row Position

Jul 11, 2014

I have a data set, in which Sheet 1 have a matrix of high level showing Engineer, Program and commodity relationship.

I have to create separate sheet for each program on sheet 1, each program sheet should have Engineer name and commodity name from sheet 1.

Multiple commodities and multiple engineers should be separated by definite structure.

find worksheet with input and output (manual) as expected.

Add A Textbox At The Current Position (selected Cell) With A Set Size, Fill Color, And Border Color

Nov 2, 2008

I'm trying to add a textbox at the current position (selected cell) with a set size, fill color, and border color. I found this: http://msdn.microsoft.com/en-us/libr...8(VS.80).aspx:

How To Identify Column Position Based On Lookup Value

Nov 10, 2011

I will be working with an array of data approx 500 rows by 50 columns (eg Sheet1!\$A\$1:\$CV\$500) in which I will need to check if approx 1000 unique values exist and in which column they appear in. Each unique value should not appear in more than 1 column. Once identified, I need to return the header value at the top of the relevant column.

My thoughts on approach would be perform a lookup of some sort to identify the column number and then use that within an index formula to return the header value based on the column number.

However, I'm not sure how to perform a lookup to get the column number. A match formula can, as far as I am aware, only lookup against a single column or single row.

Return Column Header Based On Column Criteria And Number Value

Feb 7, 2014

I have the following sample data set and I'm trying to return the appropriate column header based on criteria (i.e. DDD) and a number value which will be somewhere within the range of the table. In example below, the value returned should be Header 2 because the value is greater than those in Header 1 column (range H9 to L26).

Here's data table:

ZZZ5.0015.0050.00130.00
ZZ5.0015.0050.00130.00
Z5.0015.0050.00130.00
YYY5.0015.0050.00130.00
YY5.0015.0050.00130.00
Y5.0015.0050.00130.00
DDD5.0015.0050.00130.00
DD5.0015.0025.0075.00
D5.0015.0020.0065.00
RRR5.0015.0015.0045.00
RR2.5010.0010.0030.00
R1.503.0010.0025.00
UUU0.751.505.0020.00
UU0.751.505.0020.00
U0.751.505.0020.00
P0.751.505.0020.00
T0.100.105.0018.00

CriteriaNumberValue

Color Column Charts Based On Font Color Data Cells

Oct 27, 2009

I have attached the relevant spreadsheet for which I need to alter the color of the columns based on Site number ( Sheet 1). % Mortality will be represented in the Y-Axis, and the Site numbers would be on the X-Axis. All columns (% Mortality) except one will be of the same color, and the one of a different color will indicate a specific site. As an example, site 86 is colored differently. The way I require the chart to look is shown on Sheet 1.

After reading through some great posts on Ozgrid, I managed to do this using conditional formatting (Sheet 2), but that sort of falls short because I am required to add a data table to the chart, and the parameter that is indicated by the column bars happens to appear twice in the data table.

I was wondering if this can be automated maybe using VBA, but with the possibility of simply matching the color of columns with the font color of respective entry in the data series.

Return Value From Column Based On Header Value?

Jul 16, 2013

Cells A3:A500 contain product numbers, e.g. 1001, 1002, 1003 etc.. etc...
Cells G2:N2 are column headers, e.g. North2011, South2011, North2012, South2012 etc... etc...
Cell F2 is a lookup that will return a year, e.g. 2011, 2012, 2013 etc... etc...

Against each product is a "yes" if it was sold in the north or south region for each year. This could be both north and south, one or the other or none at all.

What I would like is a formula in cells E3:E500 that looks at the year in cell F2 and if there is a "yes" in either the North or South for that year to return "yes" otherwise blank.

I am unable to upload a sample document due to firewalls.

Return Column Header Based On Row Value

Mar 11, 2014

Requirement
Vendors
Evaluation Results

Sr.
ITEM DESCRIPTION
BRAND
UNIT
A-Z
AL AMEEN
COLOR WORKS
INDEX
OXFORD
PENCIL
SUPPLIES STORE
WAVES
LOWEST PRICE
SECOND LOWEST
Vendor Lowest Price
Vendor 2nd Lowest Price

[Code] .......

I have above table and wants to get the formula for getting the 1) Vendor Name of the Lowest Price per item 2) Vendor Name of the 2nd Lowest Price per item 3) and be able to copy down the formula for other items.

Delete Column Based On Column Header

Apr 29, 2009

I have a spreadsheet with several columns (all column names are in row 1).

I have 2 of column names. Note that their actual column# might change so the macro would have to look for the column name and NOT the column position ...

Dynamic Lookup Based On Column Header

Jun 21, 2013

I'm trying to calculate a figure in a report tab that reads off the data tab and looks for a specific column based on the header of that column. My explanation is admittedly poor but I've attached a sample file with an outline of what I'm trying to do which I hope makes things clearer.

Sample File.xlsx

Lookup Column Header Based On Cell Value

Aug 11, 2009

I have a spreadsheet an excerpt of which is as follows:

K L M N
1 Doors Security Vehicle Key
2 TRUE FALSE FALSE FALSE
3 FALSE TRUE TRUE FALSE
4 FALSE FALSE FALSE FALSE

Want I want to happen is to return the column header where there for a cell which contains "TRUE" in a blank column. If there isn't a cell with the value "TRUE" then to return "N/A". In the above example in blank column P for Row 2 would be "Doors", Row 3 would be "Security,Vehicle" and Row 4 would be "N/A".

In my actual spreadsheet I have 12 columns (L to W) which have the TRUE or FALSE values.

I know it has something to do with either HLOOKUP, INDEX and MATCH but can't seem to get it right. Any ideas?

Lookup Column Header Value Based On Matching Table Value

May 16, 2013

I'd like a formula that'll return the column header by matching a lookup value with a table in the second sheet.

eg: sheet 1

Name
Cell
Region

John
111-2222

[Code] .......

The formula should match the name in A2, John, with value from the table in sheet 2 and return the correct region, this case North.

Selectively Copying Cells Based On Column Header?

Jul 9, 2013

I'm trying to copy cells from a column in one workbook to cells in a column in another workbook based on what's in a header column.

Such as

Original workbook
Mat Qty
A 1
C 3
D 2
E 4
G 5
Total 15

Target workbook becomes

Mat Qty -> Mat Qty
A -> A 1
B -> B
C -> C 3
D -> D 2
E -> E 4
F -> F
G -> G 5

My code so far (which might not be worth reading) is:

Code:
Sub CopyColumnToWorkbook()
Dim sourceColumn As Range, targetColumn As Range, sourceColumnValue As Range, targetColumnValue As Range
Dim mySourceCell As Range, myTargetCell As Range

[code].....

I'm getting a runtime error ('1004'. Application-defined or object-defined error) on the Copy statement within the If sttement.

Macro To Insert Formula Based On Column Header

Feb 24, 2009

I have to use several dozen pivot tables a day. I already use a couple of macros to do a lot of the tedious formatting, but if I could automate this it would save me a lot more time. I have been trying to both write a code and modify code written by the recorder but have not even come close to anything that works. What I want is to have a macro that searches through the column header for predefined names and if it comes across one of these names it will insert a specific formula, based on its name, in the first cell below the header. Then it will autofill to the last row and move on to searching for the next predefined header name. If that name is not found, it will move to the next predefined header name. One major problem is that the formula to insert under any specific header name is based on other columns in the table that aren't always in the same range. So, not only do I need it to search for columns to place a formula in, that formula has to search for the appropriate column to find the correct value to use in the calculation.

For example, one predefined column name would be Avg Price. The formula for that column would be (Sales/Qty Sold), but these 2 columns could be located anwhere in the table. How can I get it, once it has found a column that needs a formula, to locate the correct column/cell to get the correct value from the sales and Qty Sold columns?

Delete Rows Based On Criteria While Referencing Column Header

Jun 6, 2007

I'm trying to use VBA code to delete certain rows based on a couple of criteria as follows:

1) If column header is "TERMINAL NAME" and the cell value in that column is "BONDDESK", I need the entire row deleted.

2) Similiarly, if the column header is "PRODUCT TYPE" and the cell value in that column is "CORP", that entire row needs deleted as well.

The information is coming from another source, so the columns may change from day to day, i.e, "TERMINAL NAME" may be in Column L one day but in Column O another day.....

Convert Numbers To Postitive/Negative Based On Column Header

Mar 7, 2008

I have this data source file that comes from a back office program, and basically what i want it to do is to turn the number negative in column C when the header of that column in column A is WITHDR and leave it positive when it is CONTR. What i was thinking was that it could go to column A from column C and go up each cell until the cell is not a date and multiply the column C number by -1 depending the column A header equals WITHDR and do that for every cell in column C, but I dont know how to proceed, im new at VBA.

Rearrange (sort) Columns Based On Number In Column Header String

Apr 3, 2014

I want to rearrange(sort asscending) columns based on numerical value in column header string through VBA macro. Please check attachment.

i.e. (Present Data)
# A B C D
1 col.1 col.4 col.3 col.2

(Output Data )
# A B C D
1 col.1 col.2 col.3 col.4

test.bmp‎

VBA Loop Through Worksheet And Format Each Column As Text, Or Date Based On Header

Jun 30, 2006

I am trying to figure out a way to condense my code (improve my code) Right now I am using a Macro in Excel to go through a workbook by selecting each column individually and if there is something in the header then formatting that column as text. The problem with the way I am currently doing it, is that due to size limitations on the VBA code, I can only select columns A to Z, and if there is more data in the worksheet it is not formatted. Also I would like the code to check the header and if the word date is in there, format it as a date instead. Also if there is a way to delete any invalid ranges in the workbook

Sub Format()
Dim VarFileName As String
Dim VarPath As String
Dim VarSavein As String
Dim wsheet As Worksheet
VarSavein = Sheets("sheet1").Range("C2").Value
VarFileName = Sheets("sheet1").Range("A2").Value
VarPath = Sheets("sheet1").Range("B2").Value
Workbooks.Open VarPath & VarFileName
For Each wsheet In ActiveWorkbook.Worksheets
Sheets(wsheet.Name).Select
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:= Array(1, 2), TrailingMinusNumbers:=True..........................................

VBA Code To Copy And Paste To Specific Rows Based On Matching Column Header?

Dec 7, 2011

I have a spreadsheet and I was wondering if there is a way to copy from a specific range in say A1:A5 in Sheet1 and paste into Sheet2 where the column matches cell A1 in Sheet1.

I am trying to do this in VBA and I was wondering if there is a way to do this.

Copy Column Data From Multiple Worksheets To Single Worksheet Based On Header Criteria

Oct 18, 2013

I have a workbook with many sheets of similar but not identical data. I need to extract columns from each sheet based on 5 header criteria and paste them to a single sheet. Each worksheet contains these 5 criteria.

I've been working with the VBA script I've pasted below. It's grabbing the 5 column criteria that I have in sheet 12, and comparing them to sheet 1 in the workbook, then copying them to sheet 12. This much is good, but I need the script to also return the data from the other worksheets as well. I've tried modifying the script based on other loop functions in other scripts I've found, but I'm not having any luck.

Color Fonts In Column G Based On Contents In Column E

Jan 21, 2009

I have created an Excel spreadsheet teachers schedule for a small school with 8 teachers. I have assigned a number to each teacher (1 - 8) so that a number typed in a cell in Column E will cause a teachers name to appear in a cell in Column G. The ranges are E3:E20 and G3:G20. I hope to find a Macro that will display each teachers name in a different color.

Return Column Header If Row Header And Value Is Known

Nov 18, 2011

I've been trying find an appropriate formula to extract the column header from a table in a different sheet if the row header and value in that table is known.

in the lookup table the row titles (column A) are product codes, column titles (row 2, D through AX) are business names and the table values are quantity.

In a different table I have product codes in column A and in column B i have the max number/quantity of products for that code. In column C i want to put the company name associated with the product and the number/quantity.

Hide Row Based On Color Column I Being Present

Nov 5, 2009

a way to hide used rows if the color of the cell in column I is green( color 35 vba) but only if the cell is visible will the macro change the rows visible state, in other words already hidden rows will remain hidden regardless if they match the color requirement.

below is some pseudo code to try to explain better what I'm trying to do. The below code doesn't actually work but I was wondering if someone knew some code that would actually do what I've described.

Color Row Based On Blank Cell In Column F?

Sep 6, 2012

I have the following data:

I would like to count up from the last row and color row with blank cells in column F light blue, but only through Column F. THe result would look something like this:

Change Color Of Entire Row Based On Value Of Single Column

Aug 4, 2006

I am looking to change to color of an entire row based on the value of a single column in that row....Say I have an issue on a line and the "priority column is set to high" I would like that entire row to turn red....

Background Color Of Cell Based On Colors In Column

Sep 28, 2013

I have a data base in which I classify cells in three colors (green, yellow, and red) on my own. I would like the first cell in each column to indicate if there is a red or yellow cell in it's column. Because red cells are more important then yellow cells, if there is a yellow and a red cell in the column, I would like the red to over ride the yellow and the first cell would indicate red.

So basically, if there is a red cell in the column, no matter what other colors there are, the first cell should show red. If there is only yellow or yellow and green cells, the first cell should show yellow. If there are only green cells or green cells and clear, the first cell should show clear.

To Change The Color Of An Entire Row Based On The Value Of A Single Column

Aug 4, 2006

I am looking to change to color of an entire row based on the value of a single collumn in that row....Say I have an issue on a line and the "priority collumn is set to high" I would like that entire row to turn red....

Countif Based On Alpha And Color Of Cell In Same Row But Absolute Column Reference

Apr 28, 2014

I am currently using a color count function to both count by color and count by color and cells containing certain characters, such as # or %. This works great.

However, I need to modify the UDF to count all the cells in a range in columns C:Z that contain alpha, AND IF the Interior.ColorIndex of a cell in column B within the same row of the counting formula equals the Interior.ColorIndex of a cell in column B within the same row of the selected cell in the range.

Basically, Column B is a header row, and I want to count the cells in a range in each column C:Z if they contain a name AND their corresponding header cell's color in column B matches the color of the header cell in the row containing the formula.

Format Row Cell Color Based On Number Of Text Strings In A Column

Jul 29, 2013

I have been trying to format the rows on this sheet to color scale red based on the number of repeat text strings in Column E. Referring to the attached example sheet, '321/312.2/321.3' appears the most times and the goal is to color code the rows it appears in the deepest shade of red, then the next highest occurring string would shade the rows it appears in a lighter shade, etc, in descending order. Our team currently does this across multiple sheets manually everyday and it would be a real time saver if we could get excel to do this automatically.