Microsoft Office 2007 professional (aca...
March 29, 2010 at 03:46:51
Specs: Windows Vista
 Hello,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.

#1
March 29, 2010 at 05:57:08
 Hi,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:=CHAR(66+COUNTA(C4:Z4))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:=IF(COUNTA(C4:IV4)>24, CHAR(64+INT((COUNTA(C4:IV4)+1)/26))& CHAR(66+COUNTA(C4:IV4)-((INT((COUNTA(C4:IV4)+1)/26))*26)), CHAR(66+COUNTA(C4:Z4)))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 SubNote 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.Regards

Report •

#2
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 FunctionRight-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:=CHAR(ColLetter(C1)+64)

Report •

#3
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.http://www.imgshack.info/images/l2v...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

#4
March 31, 2010 at 05:06:28
 Hi,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 - orangeSecond: =IF(M12<>"",TRUE,FALSE)and set color - greenThird: =IF(L12<>"",TRUE,FALSE)and set color - redRegardsPS I could not access the image:I received this message:ForbiddenYou 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 •

#5
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 •