Conditional format based on cell value in same column, different row

[quackdad]

I have several documents listed in a cross-reference chart, so I can tell when one document is referenced by another. I've place an X where they intersect. I can click on a document on the left side and highlight the entire row (or on a document along the top and highlight the entire column), find the X's and see which documents they refer to along the top. What I want to be able to have happen is that when I click on the document on the left side, then wherever there is an X in that row, the cell at the top of the column is highlighted.

To put it another way, I want to format a cell if another cell in the same column has the value X (or is not blank) and is in the active row. And, of course, to do the converse with the rows.

I'd post something I've tried, but I haven't the foggiest idea of what to do other than I've set up formulas for currentColumn and currentRow.

 

[etaf]

select the header row

then use

=COUNTIF(A:A,"X")>0

for 2007 or 2010 excel version
Conditional Formatting


Highlight applicable range >>
$A$1:$M$1


Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:


=COUNTIF(A:A,"X")>0


Format… [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

Excel Workbook
	A	B	C	D	E	F	G
1	a	b	c	c
2		x
3
4
5			x				x
6
7
8
9			x	x
10
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	1. / Formula is =COUNTIF(A:A,"X")>0	Abc
B1	1. / Formula is =COUNTIF(A:A,"X")>0	Abc
C1	1. / Formula is =COUNTIF(A:A,"X")>0	Abc
D1	1. / Formula is =COUNTIF(A:A,"X")>0	Abc
E1	1. / Formula is =COUNTIF(A:A,"X")>0	Abc
F1	1. / Formula is =COUNTIF(A:A,"X")>0	Abc
G1	1. / Formula is =COUNTIF(A:A,"X")>0	Abc

 

[quackdad]

Thank you, Wayne. It's a start. But in my chart, Column A also has a list of documents. What I want to have happen is that, using your example, if I clicked on A5, only the column headers that have X's in that row will be highlighted (i.e., C1 and G1).

 

[etaf]

I suspect that requires VBA/Macro to work as it needs to be trigged from the cell you are on
unfortunately not my area of expertise

 

[quackdad]

Thanks again, Wayne. I hadn't thought of trying this as a VBA problem. I'll put some thought into it. Meanwhile, if anyone else has a solution, VBA or otherwise, feel free to weigh in (I may be able to some up with something, but I'm sure it would be nowhere near as elegant as what someone with genuine expertise might achieve).

 

[quackdad]

OK - thanks to Wayne pointing me in the right direction, I've arrived at an answer.

I've set up my example with Column headers in B1:F1, and Row Headers ("Siders") in A2:A6, so my active field is B2:F6. The cells in Column G and Row 7 are hidden and filled with dummy text ("Stop"). Also hidden are the cells I've used to determine the Current Row (I4) and Current Column (I6) and what I call an Endrun cell (I2), which I use to place content "Endrun" or be empty in order to prevent or allow a macro from tripping at an inopportune time. I've labeled the sheet "Sample".

The code for worksheet "Sample":

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    [curRow] = ActiveCell.Row
    [curCol] = ActiveCell.Column
    
    If Range("I2") = "" Then
    
        If Not Intersect(Target, Range("A2:A6")) Is Nothing Then
            Range("A1:F6").Font.Color = RGB(0, 0, 0)
            Application.Run "Header"
        End If
    
        If Not Intersect(Target, Range("B1:F1")) Is Nothing Then
            Range("A1:F6").Font.Color = RGB(0, 0, 0)
            Application.Run "Sider"
        End If
    
    End If
    
End Sub

I've place the following 2 macros in a module (in order to prevent screen updating until I want it):

Private Sub Header()
    Application.ScreenUpdating = False
    Range("I2") = "Endrun"
    
    With Sheets("Sample")
        Set rngCell = Cells(Range("I4").Value, 2)
        Do Until rngCell.Value = "Stop"
            If rngCell.Value = "X" Then
                curCol = rngCell.Column
                rngCell.Select
                With Selection
                    rngCell.Font.Color = RGB(255, 0, 0)
                    Cells(1, curCol).Font.Color = RGB(255, 0, 0)
                End With
            End If
            Set rngCell = rngCell.Offset(0, 1)
        Loop
    End With
    
    Cells(Range("I4").Value, 1).Select
        Selection.Font.Color = RGB(255, 0, 0)
    Range("I2").ClearContents
    Application.ScreenUpdating = True
    
End Sub

Private Sub Sider()
    Application.ScreenUpdating = False
    Range("I2") = "Endrun"
    
    With Sheets("Sample")
        Set rngCell = Cells(2, Range("I6").Value)
        Do Until rngCell.Value = "Stop"
            If rngCell.Value = "X" Then
                curRow = rngCell.Row
                rngCell.Select
                With Selection
                    rngCell.Font.Color = RGB(255, 0, 0)
                    Cells(curRow, 1).Font.Color = RGB(255, 0, 0)
                End With
            End If
            Set rngCell = rngCell.Offset(1, 0)
        Loop
    End With
    
    Cells(1, Range("I6").Value).Select
        Selection.Font.Color = RGB(255, 0, 0)
    Range("I2").ClearContents
    Application.ScreenUpdating = True
    
End Sub

Any non-malicious critique is welcomed.