PDA

View Full Version : Solved: Conditional Formatting a worksheet based on value in a cell in another worksheet



bananatang
08-11-2009, 06:18 AM
Hi,

Need further assistance please...........

I have a summary sheet which lists pupils names. Each pupil has a separate worksheet and the worksheet is called the pupils name.

I am trying to conditional format column E in the summary sheet, starting from Row 14. The cell which i want the conditional format to look at is in the pupils named worksheet at U13.

The Pupils name is listed in the summary sheet at Column C, Row14

The vba code will need to look at column C Row14 in the summary sheet starting from for the pupils name and then goto the pupil named worksheet. Check to see if cell U13 is populated, and if it is i would like the cell in Column E in the summary sheet, adjacent to the pupils name to go to Red if populated and Green if not.

The pupil list will increase over time, thus the vba code will need ensure that it captures all the pupils.

I have attached a sample of the document for easier understanding of the issue i am trying to solve.

Many Thanks

BT

Bob Phillips
08-11-2009, 08:48 AM
Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For i = 14 To LastRow Step 2

If Worksheets(.Cells(i, "C").Value).Range("U13").Value <> "" Then

.Cells(i, "E").Interior.ColorIndex = 3
Else

.Cells(i, "E").Interior.ColorIndex = 14
End If
Next i
End With
End Sub

macropod
08-11-2009, 09:40 AM
Hi BT,

Here's some code you could put in the 'Summary Sheet' module, to make the colour changes event-driven:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer, ws As Worksheet
With ThisWorkbook.Worksheets("Summary Sheet")
For i = 14 To .UsedRange.Rows.Count Step 2
For Each ws In ThisWorkbook.Worksheets
If ws.Visible = xlSheetVisible Then
If ws.Name <> "Summary Sheet" And ws.Name <> "Name Ranges" Then
If .Cells(i, 3).Value <> 0 Then .Cells(i, 5).Interior.ColorIndex = 3
If ws.Range("I4").Value = .Cells(i, 3).Value Then
.Cells(i, 5).Interior.ColorIndex = 8
Exit For
End If
End If
End If
Next
Next
End With
End SubIf you want to drive the code by some other means, just insert it into a standard sub in the appropriate module.

Also, instead of keying the names into I4 on each sheet (if that's what you're doing), you could use the formula:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1))- FIND("]",CELL("filename",A1)))
to automatically insert & update the worksheet name there.

bananatang
08-12-2009, 06:12 AM
Xld and Macropod, thank you both for your code.

BT