Advanced cell formating

Microsoft Office 2007 professional (aca...
March 29, 2010 at 03:46:51
Specs: Windows Vista

First off all iv read this topic "excel cell color based on second cell color" and tryed to change it in order to fix my problem but its just to different.
Ill try and explain it abit.
I have a worksheet with unlimited amount off rows and so far 20 collumns. I have a freeze pane so that row 1 and collumn A&B are locked. What i'm trying to achiev is that the cells in column B will be fileld with a certain color depending on what cell contains information.
Lets say B1,C1,D1 and E1 contains information. This means B1 will be filled lets say green because D1 contains information.
Now B2,C2,D2,E2,F2 and G2 contains information. Because D2 has something in its cell B2 is filled green, however because in this row G2 has information B3 should be filled blue.

So the idea is that i can look at the column B and based on its fill color i can see how far along i am in that row.
Also this should be repeateble for each new row created without i have to manually change the macro to match the row number.

This is somewhat my problem. I know that Excell may not be the best program for this but i have to do with whats available for me.

See More: Advanced cell formating

March 29, 2010 at 05:57:08

You could use conditional formatting, but as you need a different color for every last column used, it becomes difficult to exactly match a color to a column.

If there are no empty cells in your rows, before the last used cell (for example if F4 is the last used cell on row 4, then cells C4, D4 and E4 are all used),
then you could try this: In cell B4 enter this formula:

It will place the letter of the last column used, in cell B4. Just drag the formula to extend it to other rows.

This works for columns up to Z. If you intend going into column AA and beyond, then a more complex formula is required:

Note that I have split this formula onto four lines for ease of viewing.

I also have a macro that adds colors to cells in column B. I have not used the additional conditional formatting options available in Excel 2007 a lot, so I may be missing an easy way to do this with CF.

Anyway here is a way to do it with code.
Right-click the name tab of your worksheet, at the bottom of the screen. Select View code and paste this into the Visual Basic code window:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim objIsect As Range
Dim rngCell As Range
Dim dblClr As Double
Dim rngLast As Range

'create a range if the cell changed (Target) falls within C2 to Z1000
'change the range as appropriate
Set objIsect = Intersect(Target, ActiveSheet.Range("C2:Z1000"))

'test if the changed cell was in our range
If Not objIsect Is Nothing Then
    'find the last used column in the target row
    Set rngLast = ActiveSheet.Range("Z" & Target.Row).End(xlToLeft)
    'set the color in cell B based on the column number
    Select Case rngLast.Column
        Case 3
            dblClr = RGB(250, 200, 80)
        Case 4
            dblClr = RGB(250, 200, 60)
        Case 5
            dblClr = RGB(250, 200, 40)
        Case 6
            dblClr = RGB(250, 200, 20)
        Case 7
            dblClr = RGB(250, 180, 100)
        Case 8
            dblClr = RGB(250, 160, 100)
        Case 9
            dblClr = RGB(250, 140, 100)
        Case 10
            dblClr = RGB(250, 120, 100)
	'add more columns and colors here as required
        Case Else
            dblClr = RGB(255, 255, 255)
    End Select
    'set target row, column B color
    ActiveSheet.Range("B" & Target.Row).Interior.Color = dblClr
End If
End Sub

Note that the range B2 to Z1000 should be changed to match the cells that you are working with and which will trigger the color change.
Add additional 'Cases' to cover the maximum number of columns likely to be used. I limited the number of 'Cases' shown so that this post wasn't too long.


Report •

March 29, 2010 at 07:08:34
How about something like this:

Insert a Module and paste this code in:

Function ColLetter(ByVal Target As Range)
 ColLetter = Cells(Target.Row, Columns.Count).End(xlToLeft).Column
End Function

Right-Click the sheet tab and paste this code in:

Private Sub Worksheet_Change(ByVal Target As Range)
 Cells(Target.Row, "B").Interior.ColorIndex = ColLetter(Target)
End Sub

The function will determine the number of the last Column used and the Change code will set the color of Column B based on that color.

It'll be good up to 56 colors and could be adjusted by adding or subtracting values.

If you want to be able to read the Column Letter, you could insert a column (C?) and just use this function, which will get you up to Z:


Report •

March 31, 2010 at 03:32:24
Thank you for the replys, they are off much help.

But its not working like i was hopping it would because there are empty cells inbetween. I probably didnt explain it right so i took a screenshot.

I changed the idea a little bit beause i feel it might be easier to make it work.
I used Humar's solution and it did what it was supposed to do but not what i wanted it to do. I tryed to alter it so it would fill my needs but alas, i cant get it to work.
Is it possible to set certain range like from column "I" to "M" and if "L" has a value "I" would turn red and if "M" has a value "I" would turn green and if neither are used "I" stays orange? This has to be repeateble for a few more pre set ranges, like "C" upto "H" and "N" upto "H"?

iv used
Set objIsect = Intersect(Target, ActiveSheet.Range("I:M")) but it appears not to work.
Any idea's what im doing wrong?

Report •

Related Solutions

March 31, 2010 at 05:06:28

Using your modification:

Set objIsect = Intersect(Target, ActiveSheet.Range("I:M")) 
worked when I tested it.
The code only responded to data entered in columns I to M, and colored the cell in column B on the same row.

However, what you want to do is now quite different to your original request.
You said if "L" has a value "I" would turn red and if "M" has a value "I" would turn green and if neither are used "I" stays orange.
1. Does this mean that there is no color change if data is in a cell in column J or K.
2. What happens if there is data in both columns L and M.

If all you want is for the cell in column I to be orange if both L & M are empty, green if M contains data (irrespective of the status of L) and finally red if L contains data, then this is a fairly straightforward conditional format.
Use three 'Formula Is:' formulas as follows:
First: =IF(AND(L12="",M12=""),TRUE,FALSE)
and set color - orange
Second: =IF(M12<>"",TRUE,FALSE)
and set color - green
Third: =IF(L12<>"",TRUE,FALSE)
and set color - red

PS I could not access the image:
I received this message:
You don't have permission to access /images/l2vu3j96bzvpme7ybu.jpg on this server.
Additionally, a 404 Not Found error was encountered while trying to use an ErrorDocument to handle the request.

Report •

March 31, 2010 at 05:17:45
I can't download the screen shot at work, so I'm kind of flying blind here...

If you've changed the game so that you are only formatting certain columns based on checking other columns (e.g. formatting I by checking L and M) then why not just use Conditional Formatting with 3 conditions, in this order?

=IF(M1<>"", TRUE, FALSE) Format Green
=IF(L1<>"", TRUE, FALSE) Format Red
=IF(AND(L1="",M1=""),TRUE,FALSE) Format Orange

Report •

Ask Question