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

[tommill52]

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]

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.