Excel VB Set Background Colour of Variable Range of Cells in Same Row According to Lookup Value in Another Worksheet

tommill52

New Member
Joined
May 12, 2015
Messages
27
Hi All, I would be really grateful for some help on this one (Excel 2007 onwards)

I have two worksheets, 'Analysis1' and 'Support'.

'Analysis1' is generated from a number of sources, and I have no problem in getting the data into it. The number of columns in this worksheet can vary, but the first three are always the same. The problem I have is that I need to set the background colour of the cells in row 2, columns 'D' to 'last column'. The colour will be either standard blue, yellow, or green.

The colour is determined by looking up, in turn, the values in row 2, columns 'D' to 'last column' in worksheet 'support'. These values are 4-character codes (eg, 10CC, 10CH, 20DS).

Worksheet 'Support' contains a list of these codes in column 'A', the definition of the code in col 'B' (irrelevant to this exercise), and the colour name (blue, yellow, or green) in column 'D'.

So, I need to look up the value in 'Analysis1.D2' in Column A of 'Support', and use the value there to apply a background colour to 'Analysis.D2'.

I then need to do the same to 'Analysis.E2' to the last column (row2) in worksheet 'Analysis1'.

If you could help with this it would be great. I have tried various things but I am no VB expert and this seems very complex to me.


Also, once this is done, I need to be able to add the values in each row (from row 3 to last row) for the cells under each of these headers and sum them in separate columns for each colour at the right hand side of worksheet 'Analysis' (starting at 'last column'+2). So, I have a column for each of the colours (blue, yellow and green).

I have worked out how to create these columns and create a range for the 'sum' action, but I am having problems working out how to include them in the summing - I am guessing I need to do something with 'SUMIFS?
 

tommill52

New Member
Joined
May 12, 2015
Messages
27
Hi All, I would be really grateful for some help on this one (Excel 2007 onwards)

I have two worksheets, 'Analysis1' and 'Support'.

'Analysis1' is generated from a number of sources, and I have no problem in getting the data into it. The number of columns in this worksheet can vary, but the first three are always the same. The problem I have is that I need to set the background colour of the cells in row 2, columns 'D' to 'last column'. The colour will be either standard blue, yellow, or green.

The colour is determined by looking up, in turn, the values in row 2, columns 'D' to 'last column' in worksheet 'support'. These values are 4-character codes (eg, 10CC, 10CH, 20DS).

Worksheet 'Support' contains a list of these codes in column 'A', the definition of the code in col 'B' (irrelevant to this exercise), and the colour name (blue, yellow, or green) in column 'D'.

So, I need to look up the value in 'Analysis1.D2' in Column A of 'Support', and use the value there to apply a background colour to 'Analysis.D2'.

I then need to do the same to 'Analysis.E2' to the last column (row2) in worksheet 'Analysis1'.

If you could help with this it would be great. I have tried various things but I am no VB expert and this seems very complex to me.


Also, once this is done, I need to be able to add the values in each row (from row 3 to last row) for the cells under each of these headers and sum them in separate columns for each colour at the right hand side of worksheet 'Analysis' (starting at 'last column'+2). So, I have a column for each of the colours (blue, yellow and green).

I have worked out how to create these columns and create a range for the 'sum' action, but I am having problems working out how to include them in the summing - I am guessing I need to do something with 'SUMIFS?
​Update
I have managed to work out how to get the vLookup working to assign a colour to a cell based on the lookup, but this has raised an additional issue. The code I am using is

vCellFormula = Application.WorksheetFunction.VLookup(vCell, vRange, 4)

where vCellFormula is the lookup value found (eg 'Green')
vCell is the cell that is being used as the basis of the lookup (eg D2, where D2 contains '10CC Val')

The problem is that I need to look up a trimmed version of the content of D2, ie the first four characters only (ie '10CC'). My vLookup is using a cell reference not its content.

I will work on the 'SUM' element of my original question once I have this issue resolved. If anyone can help I would be grateful.
 

Forum statistics

Threads
1,082,153
Messages
5,363,459
Members
400,737
Latest member
urhen22

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top