Colour a row if eg. a2 is red or c2 is red

[helenmeyer]

Hi,

How do I colour an entire row if certain cells are filled with a colour?

eg:

A2 is red, F2 is blue etc then the entire row would colour yellow or even P2 would colour yellow


Many thanks
H

[Glenn Kennedy]

It depends on how you coloured A2 red. If it was done simply by applying shading - you will need VBA, as the color is NOT data. If it was done by conditional formatting, then you can use the same rule to apply to other cell(s) row(s).

So, how was it made red?

[helenmeyer]

It was manually coloured :-(

[Glenn Kennedy]

So is there any reason why you manually colored A2 red. Like - the value was less than 100, or what??

More details needed!!!! Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[helenmeyer]

I didn't manually color the cells - I was sent the spreadsheet.

I have attached a small sample workbook. The data is an extract of my workbook so is truly representative of my real data.

Many thanks for your help.

[Glenn Kennedy]

A different question, now that I can see what you want. You do need a little VBA.

Please Login or Register  to view this content.

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
[B][I]

Then with the colour you want to count filled in somewhere as a reference cell (I used C2), use this formula:

=IF(CountCellsByColor(J4:AA4,$C$2)>0,"SI","")

If you change the shading of any cells, you need to refresh the sheet, by hitting F9 (Data/Calculation/Calculate Now)