Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced "Conditional" formatting which changes by cell selection
Hi,
I'll try to explain my problem. In column A i can have 5 different numbers: 1,2,3,4 and 5. So the value of any cell in column A (except for rows 1,2,3,4,5 - they are heading rows ) can be 1 to 5. Row 1,2,3,4 and 5 are heading rows. This is what i would like: when I'm in some row (for example row 34 - and the value of cell A34 is for example 3), and certain column(lets say column H), I would like cell H3 to become let's say bold(or change background color or both) - H3 because when the value in cell A34 is 3 that means that heading row 3 is used and needs to be bolded (but now the whole row just the cell above my selected cell but in the appropriate heading row - so if A34 is 1 cell H1 will be bolded and so on). I have some knowledge of VB so if I need to use VB here I think i could manage. If you have any sugestions it would mean a lot to me! If you don't understand :) my problem plese say and I'll try to explain again :) Cheers, Marko vaco |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced "Conditional" formatting which changes by cell selection
have you tried conditional formatting using a formula rather than value?
"cosva" wrote: Hi, I'll try to explain my problem. In column A i can have 5 different numbers: 1,2,3,4 and 5. So the value of any cell in column A (except for rows 1,2,3,4,5 - they are heading rows ) can be 1 to 5. Row 1,2,3,4 and 5 are heading rows. This is what i would like: when I'm in some row (for example row 34 - and the value of cell A34 is for example 3), and certain column(lets say column H), I would like cell H3 to become let's say bold(or change background color or both) - H3 because when the value in cell A34 is 3 that means that heading row 3 is used and needs to be bolded (but now the whole row just the cell above my selected cell but in the appropriate heading row - so if A34 is 1 cell H1 will be bolded and so on). I have some knowledge of VB so if I need to use VB here I think i could manage. If you have any sugestions it would mean a lot to me! If you don't understand :) my problem plese say and I'll try to explain again :) Cheers, Marko vaco |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced "Conditional" formatting which changes by cell selection
On 23 list, 17:26, Smallweed
wrote: have you tried conditional formatting using a formula rather than value? "cosva" wrote: Hi, I'll try to explain my problem. In column A i can have 5 different numbers: 1,2,3,4 and 5. So the value of any cell in column A (except for rows 1,2,3,4,5 - they are heading rows ) can be 1 to 5. Row 1,2,3,4 and 5 are heading rows. This is what i would like: when I'm in some row (for example row 34 - and the value of cell A34 is for example 3), and certain column(lets say column H), I would like cell H3 to become let's say bold(or change background color or both) - H3 because when the value in cell A34 is 3 that means that heading row 3 is used and needs to be bolded (but now the whole row just the cell above my selected cell but in the appropriate heading row - so if A34 is 1 cell H1 will be bolded and so on). I have some knowledge of VB so if I need to use VB here I think i could manage. If you have any sugestions it would mean a lot to me! If you don't understand :) my problem plese say and I'll try to explain again :) Cheers, Marko vaco I don't know how to start!! :( Any idea? Marko Svaco |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced "Conditional" formatting which changes by cell selection
On 23 list, 19:12, cosva wrote:
On 23 list, 17:26, Smallweed wrote: have you tried conditional formatting using a formula rather than value? "cosva" wrote: Hi, I'll try to explain my problem. In column A i can have 5 different numbers: 1,2,3,4 and 5. So the value of any cell in column A (except for rows 1,2,3,4,5 - they are heading rows ) can be 1 to 5. Row 1,2,3,4 and 5 are heading rows. This is what i would like: when I'm in some row (for example row 34 - and the value of cell A34 is for example 3), and certain column(lets say column H), I would like cell H3 to become let's say bold(or change background color or both) - H3 because when the value in cell A34 is 3 that means that heading row 3 is used and needs to be bolded (but now the whole row just the cell above my selected cell but in the appropriate heading row - so if A34 is 1 cell H1 will be bolded and so on). I have some knowledge of VB so if I need to use VB here I think i could manage. If you have any sugestions it would mean a lot to me! If you don't understand :) my problem plese say and I'll try to explain again :) Cheers, Marko vaco I don't know how to start!! :( Any idea? Marko Svaco And conditional formatting works with 3 conditions? I need five conditions. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced "Conditional" formatting which changes by cell selection
On Oct 23, 6:14 pm, cosva wrote:
On 23 list, 19:12, cosva wrote: On 23 list, 17:26, Smallweed wrote: have you tried conditional formatting using a formula rather than value? "cosva" wrote: Hi, I'll try to explain my problem. In column A i can have 5 different numbers: 1,2,3,4 and 5. So the value of any cell in column A (except for rows 1,2,3,4,5 - they are heading rows ) can be 1 to 5. Row 1,2,3,4 and 5 are heading rows. This is what i would like: when I'm in some row (for example row 34 - and the value of cell A34 is for example 3), and certain column(lets say column H), I would like cell H3 to become let's say bold(or change background color or both) - H3 because when the value in cell A34 is 3 that means that heading row 3 is used and needs to be bolded (but now the whole row just the cell above my selected cell but in the appropriate heading row - so if A34 is 1 cell H1 will be bolded and so on). I have some knowledge of VB so if I need to use VB here I think i could manage. If you have any sugestions it would mean a lot to me! If you don't understand :) my problem plese say and I'll try to explain again :) Cheers, Marko vaco I don't know how to start!! :( Any idea? Marko Svaco And conditional formatting works with 3 conditions? I need five conditions.- If I understand you correctly you don't need 5 conditions; you need one condition in each of cells H1 to H2, i.e. H1: bold if column A of currently selected row = 1, normal otherwise H2: bold if column A of currently selected row = 2, normal otherwise etc. To do that you could use conditional formatting using the formula option. There are two problems about this (both qualified by "as far as I know"): 1. There is no worksheet function to tell you anything about what the current selection is, or what it contains. To get round this you can write a UDF: (beware word-wrap) Public Function ValueInSelectionColumnA() Application.Volatile ' force recalc even if VBA thinks it's not needed On Error Resume Next ' avoid error if Selection is not a range of cells ValueInSelectionColumnA = ActiveSheet.Cells(Selection.Cells(1, 1).Row, 1).Value On Error GoTo 0 End Function Then in H1 to H5 set up Conditional Formatting with a formula of =ValueInSelectionColumnA()=ROW() , format Bold This sort of works, but the formatting only works when you recalculate, not if you just move around the sheet (Problem 2). To get round that you need to use the SelectionChange event for the worksheet: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim iRow As Integer For iRow = 1 To 5 ActiveSheet.Cells(iRow, "H").Formula = ActiveSheet.Cells(iRow, "H").Formula ' force recalc Next End Sub Doing all this seems to achieve what I think you want - I hope it makes some sense Andrew |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting on "Any Text" entered in a cell | Excel Discussion (Misc queries) | |||
"justify across selection" cell formatting | Excel Discussion (Misc queries) | |||
Excel 2003: Conditional Formatting using "MIN" & "MAX" function | Excel Discussion (Misc queries) | |||
conditional formatting "if part of cell contents contains string" | Excel Worksheet Functions | |||
excel should "paste special" a "conditional formatting" | Excel Programming |