Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change formats based on A:A cell value on each row - (not with conditional format!) ?
Hi,
I need VBA code for this, I can't use the conditional format function of some reasons. Let ? = a row between 6 and 96 and cells in same row between column C:G should have different fonts, colorindex etc depending on what letter there is in the cell in A column. If A? = "K" then cells Range("C?:G?").ColorIndex = 1 If A? = "I" then cells Range("C?:G?").ColorIndex = 2 If A? = "R" then cells Range("C?:G?").ColorIndex = 3 Any suggestions? By the way, the fastest way possible please, the VBA code will be into worksheet_change. Maybe use "Find" rather than a Loop, maybe? /Regards |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change formats based on A:A cell value on each row - (not with conditional format!) ?
for i = 6 to 96
If Range("A6")(i).value = "K" then cells Range("C5:G5").Offset(i,0).ColorIndex = 1 If Range("A6")(i).value = "I" then cells Range("C5:G5").Offset(i,0).ColorIndex = 2 If Range("A6")(i).value = "R" then cells Range("C5:G5").Offset(i,0).ColorIndex = 3 Next -- Regards, Tom Ogilvy "Marie J-son" wrote in message ... Hi, I need VBA code for this, I can't use the conditional format function of some reasons. Let ? = a row between 6 and 96 and cells in same row between column C:G should have different fonts, colorindex etc depending on what letter there is in the cell in A column. If A? = "K" then cells Range("C?:G?").ColorIndex = 1 If A? = "I" then cells Range("C?:G?").ColorIndex = 2 If A? = "R" then cells Range("C?:G?").ColorIndex = 3 Any suggestions? By the way, the fastest way possible please, the VBA code will be into worksheet_change. Maybe use "Find" rather than a Loop, maybe? /Regards |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change formats based on A:A cell value on each row - (not with conditional format!) ?
Why can't you use CF?
-- HTH RP (remove nothere from the email address if mailing direct) "Marie J-son" wrote in message ... Hi, I need VBA code for this, I can't use the conditional format function of some reasons. Let ? = a row between 6 and 96 and cells in same row between column C:G should have different fonts, colorindex etc depending on what letter there is in the cell in A column. If A? = "K" then cells Range("C?:G?").ColorIndex = 1 If A? = "I" then cells Range("C?:G?").ColorIndex = 2 If A? = "R" then cells Range("C?:G?").ColorIndex = 3 Any suggestions? By the way, the fastest way possible please, the VBA code will be into worksheet_change. Maybe use "Find" rather than a Loop, maybe? /Regards |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change formats based on A:A cell value on each row - (not with conditional format!) ?
Because I actually already use CF as one of the formats to be
restored...This VBA code is used with worksheet_change to restore formats in case of drag and drop etc., where also CF will be overwritten. Actually, Worksheet_Change is not compleatly sufficient either. There are events that doesn't trig with WS_change and I'm not fully in control if there had to be more codes needed to cover other events. Open question: Are there any other events to be covered? We are talking aboute ensure that the cell formats will continue to "look right": I delete comments, verification.delete and all boxes, fonts and boardes you can record when you change Format-Cells; Format Cells window and change or delete Conditional Formats. /Regards "Bob Phillips" skrev i meddelandet ... Why can't you use CF? -- HTH RP (remove nothere from the email address if mailing direct) "Marie J-son" wrote in message ... Hi, I need VBA code for this, I can't use the conditional format function of some reasons. Let ? = a row between 6 and 96 and cells in same row between column C:G should have different fonts, colorindex etc depending on what letter there is in the cell in A column. If A? = "K" then cells Range("C?:G?").ColorIndex = 1 If A? = "I" then cells Range("C?:G?").ColorIndex = 2 If A? = "R" then cells Range("C?:G?").ColorIndex = 3 Any suggestions? By the way, the fastest way possible please, the VBA code will be into worksheet_change. Maybe use "Find" rather than a Loop, maybe? /Regards |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change formats based on A:A cell value on each row - (not with conditional format!) ?
Because I actually already use CF as one of the formats to be
restored...This VBA code is used with worksheet_change to restore formats in case of drag and drop etc., where also CF will be overwritten. So why not replace your original CF's Sub Test() On Error GoTo errH Application.EnableEvents = False With ActiveSheet.Range("c6") .Activate ' With .FormatConditions .Delete .Add xlExpression, , "=$A6=""K""" .Item(1).Interior.ColorIndex = 3 'red .Add xlExpression, , "=$A6=""I""" .Item(2).Interior.ColorIndex = 4 'green .Add xlExpression, , "=$A6=""R""" .Item(3).Interior.ColorIndex = 5 'blue End With .Copy .Resize(91, 5).PasteSpecial xlPasteFormats ' c6:g96 End With errH: Application.EnableEvents = True End Sub If your old CF's might have been dragged elsewhere, maybe you want to delete all CF's in the sheet at the head of the code. ActiveSheet.UsedRange.FormatConditions.Delete Running the above should be faster than looping and changing formats. However if your values K, I & R in col-A don't change regularly, I would prefer to format with normal interior colour formats. A bit longer but if you're running from a macro from time to time perhaps not an issue. Open question: Are there any other events to be covered? We are talking aboute ensure that the cell formats will continue to "look right": Not sure you mean by to be covered, in terms of what? But you are correct that not all cell changes trigger an event. Regards, Peter T "Marie J-son" wrote in message ... Because I actually already use CF as one of the formats to be restored...This VBA code is used with worksheet_change to restore formats in case of drag and drop etc., where also CF will be overwritten. Actually, Worksheet_Change is not compleatly sufficient either. There are events that doesn't trig with WS_change and I'm not fully in control if there had to be more codes needed to cover other events. Open question: Are there any other events to be covered? We are talking aboute ensure that the cell formats will continue to "look right": I delete comments, verification.delete and all boxes, fonts and boardes you can record when you change Format-Cells; Format Cells window and change or delete Conditional Formats. /Regards "Bob Phillips" skrev i meddelandet ... Why can't you use CF? -- HTH RP (remove nothere from the email address if mailing direct) "Marie J-son" wrote in message ... Hi, I need VBA code for this, I can't use the conditional format function of some reasons. Let ? = a row between 6 and 96 and cells in same row between column C:G should have different fonts, colorindex etc depending on what letter there is in the cell in A column. If A? = "K" then cells Range("C?:G?").ColorIndex = 1 If A? = "I" then cells Range("C?:G?").ColorIndex = 2 If A? = "R" then cells Range("C?:G?").ColorIndex = 3 Any suggestions? By the way, the fastest way possible please, the VBA code will be into worksheet_change. Maybe use "Find" rather than a Loop, maybe? /Regards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Format based on another cell | Excel Discussion (Misc queries) | |||
Conditional Format based on a different cell | Excel Worksheet Functions | |||
Lock cell formats (conditional format) | Excel Worksheet Functions | |||
Conditional format: change row color based on cell value | Excel Discussion (Misc queries) | |||
Conditional Format to change entire row based on one cells data | Excel Discussion (Misc queries) |