Yet another cell color question.

[smoore]

I have a worksheet with about eight columns in it. Column A is a
descending column of numbers 1 thru 22. Next is the B column with a
variety of numbers, not necessarily all the way down, and a sumation in
B25. The same for C,D, E, F, and so on. What I need is a conditonal
formatting formula that says - Find the last active cell in Column B,
then compare the cell adjacent to it in Col A to the current sumation
in B25. if they are equal, change the cell shading of B25. Then do the
same in the other columns. Thanks

 

[Guest]

Assuming any row above the last one containing data for one column, I believe
this formula would work for the conditional formatting of cell B25:
=23-counta(B1:B22)=B25

23-counta(B1:B22), for what I understood, is the value of the cell in column
A that is adjacent to the last cell containing data in column B. If I
misunderstood, it can always be changed with:
=index(A1:A22,counta(B1:B22))=B25

If you want to copy the formatting directly into the next columns, you might
want to use absolute reference for column A:
=index($A1:$A22,counta(B1:B22))=B25

Hope it helped,
Félix

 

[Ken Johnson]

Hi, I don't know of a way to get conditional formatting to do what you
want, but I think if you paste the following event procedure into the
code module of the sheet you have set up then the same effect will be
achieved. With this event procedure if you change any of the cells
above row 25, not in column A and not in any column to the right of
your set up columns, then if the new value in the changed cell is equal
to the value in column A in the same row the shading of the cell with
the SUM formula changes from light turquoise (colorindex value = 34) to
pale yellow (colorindex value = 36).

To get the code in place...

1.Copy the code
2.Right click the sheet tab. A contextual popup menu should appear.
3.Select View code from that popup.
4.Paste the code
5.Press Alt + F11 to return to the worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Problem
Dim NumCols As Integer
NumCols = Cells(25, Range("1:1").Columns.Count).End(xlToLeft).Column
If Target.Column > NumCols Or Target.Row > 24 Then Exit Sub
Application.EnableEvents = False
If Target.Value = Cells(Target.Row, 1).Value Then
Cells(25, Target.Column).Interior.ColorIndex = 36 'pale yellow fill
Else: Cells(25, Target.Column).Interior.ColorIndex = 34 'light
turquoise
End If
Problem: Application.EnableEvents = True
End Sub

Change the colorindex values to suit your needs.
You should be able to find all the values and their appearances in VBA
Help.

Ken Johnson

 

[Guest]

Oops!
Assuming any row above the last one containing data for one column >>>>>ALSO
CONTAINS DATA <<<<<, I believe this formula would work for the conditional
formatting of cell B25:
=23-counta(B1:B22)=B25

23-counta(B1:B22), for what I understood, is the value of the cell in column
A that is adjacent to the last cell containing data in column B. If I
misunderstood, it can always be changed with:
=index(A1:A22,counta(B1:B22))=B25

If you want to copy the formatting directly into the next columns, you might
want to use absolute reference for column A:
=index($A1:$A22,counta(B1:B22))=B25

Hope it helped,
Félix

 

[smoore]

Felix & Ken,
Thank you both for the quick response!

I went with felix's solution and the index function worked perfectly.

Ken, I'm sure yours would have worked equally well but my pointy little
head seems to grasp functions better than VBA . Still thanks for your
reply.

 

[Ken Johnson]

Hi,
Thanks for the feedback.
I've learned something new. At first glance I honestly thought it would
not be possible with cond formatting. Felix sure knows his stuff.
Ken Johnson