| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 91541 | 2008-07-10 06:29:00 | Dashboard design | indus (8658) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 687327 | 2008-07-10 06:29:00 | I am designing an information dashboard in excel with traffic lights using four colours. The data in the next tab with scores 1 to four assigned to the results for different stores e.g Col 1 KPi col 2 score for store 1 col 3 score for store 2 etc... For traffic lights in sheet 1 I am using symbol fro Wingding font. How can I automate the colour of the symbol, not the cell, to cahnge with the score in sheet 2? Can some one help? many thanks. indus |
indus (8658) | ||
| 687328 | 2008-07-10 07:07:00 | Have you looked at conditional formatting? Format > Conditional Formatting. | Jen (38) | ||
| 687329 | 2008-07-10 20:55:00 | Conditional formatting allows only three colours and the cell refernce in formula has to be in the same worksheet as it wont look up the reference in the next tab. | indus (8658) | ||
| 687330 | 2008-07-10 22:00:00 | You can you use basic VBA case statements to conditionally format cells with as many conditions as you like: If Not Intersect(Target, Range("A1:A10")) is Nothing Then Select Case Target Case 1 To 5 icolor = 6 Case 6 To 10 icolor = 12 Case 11 To 15 icolor = 7 Case 16 To 20 icolor = 53 Case 21 To 25 icolor = 15 Case 26 To 30 icolor = 42 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor End If End Sub And if you need to conditionally format a cell based on the contents of another cell, I'm pretty sure you can use the INDIRECT function. HTH |
nofam (9009) | ||
| 1 | |||||