Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional format rage based on entries within range
I have a spreadsheet in excel 2003 where a range of cells contain different
text. If there is no text in any of the cells within the range I would like the range conditionally formated (red), however if any or all of the cells within the range contain text then no format should be applied;e.g. If A1 to D1 are blank format cells red, when text is entered into B1 remove formatting. All help greatly recieved. Simon. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional format rage based on entries within range
Simon,
Select A1 to d1 and then format|Conditional formatting - Select 'formula is' enter the formula =A1="" Pick a colour OK -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Simon" wrote: I have a spreadsheet in excel 2003 where a range of cells contain different text. If there is no text in any of the cells within the range I would like the range conditionally formated (red), however if any or all of the cells within the range contain text then no format should be applied;e.g. If A1 to D1 are blank format cells red, when text is entered into B1 remove formatting. All help greatly recieved. Simon. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional format rage based on entries within range
Instead of CF try this worksheet change event that will color if all TRULY
blank Right click sheet tabview codeinsert this '-------- Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myrange As Range Set myrange = Range("b1:d4") If Intersect(Target, myrange) Is Nothing Then Exit Sub If Target.Count 1 Then Exit Sub If Len(Application.Trim(Target)) < 1 Then Application.EnableEvents = False Target = "" Application.EnableEvents = True End If If Application.CountA(myrange) = 0 Then myrange.Interior.ColorIndex = 3 Else myrange.Interior.ColorIndex = -4142 End If End Sub '======= -- Don Guillett Microsoft MVP Excel SalesAid Software "Simon" wrote in message ... I have a spreadsheet in excel 2003 where a range of cells contain different text. If there is no text in any of the cells within the range I would like the range conditionally formated (red), however if any or all of the cells within the range contain text then no format should be applied;e.g. If A1 to D1 are blank format cells red, when text is entered into B1 remove formatting. All help greatly recieved. Simon. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional format rage based on entries within range
Hi Mike,
that works well in terms of the formating for individual cells, but what I'm trying to do is remove the formatting from the entire cell range once one (or) more cells is filled. (The text to be inserted will be fairly random, so I can't use a validated list either.) Simon "Mike H" wrote: Simon, Select A1 to d1 and then format|Conditional formatting - Select 'formula is' enter the formula =A1="" Pick a colour OK -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Simon" wrote: I have a spreadsheet in excel 2003 where a range of cells contain different text. If there is no text in any of the cells within the range I would like the range conditionally formated (red), however if any or all of the cells within the range contain text then no format should be applied;e.g. If A1 to D1 are blank format cells red, when text is entered into B1 remove formatting. All help greatly recieved. Simon. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional format rage based on entries within range
Hi Don,
Thanks. I have no idea what the code means but it works. The only time the cells don't revert back to red if the range is empty is when two or more cells are selected at the same time and the data is deleted en mass rather than one cell at a time, in this instance the now empty range remains un formatted - certainly not the end of the world for me, as once the data is entered there should be no need to delete it, but thtought you might be interested. Also, as with all novices' I haven't given the complete picture - there are other cell ranges in the spreadsheet that I would like formatting to different colours depending on whether the range is blank or not, I tryed to duplicate your formula below the origonal I had entered but excel returned an error and (don't mean to be greedy), is there anything I can add to your code to repeat the command down the columns of the data range for a (monthly) list of varying length. Or should I stick to looking for a conditional format solution? Cheers. Simon. "Don Guillett" wrote: Instead of CF try this worksheet change event that will color if all TRULY blank Right click sheet tabview codeinsert this '-------- Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myrange As Range Set myrange = Range("b1:d4") If Intersect(Target, myrange) Is Nothing Then Exit Sub If Target.Count 1 Then Exit Sub If Len(Application.Trim(Target)) < 1 Then Application.EnableEvents = False Target = "" Application.EnableEvents = True End If If Application.CountA(myrange) = 0 Then myrange.Interior.ColorIndex = 3 Else myrange.Interior.ColorIndex = -4142 End If End Sub '======= -- Don Guillett Microsoft MVP Excel SalesAid Software "Simon" wrote in message ... I have a spreadsheet in excel 2003 where a range of cells contain different text. If there is no text in any of the cells within the range I would like the range conditionally formated (red), however if any or all of the cells within the range contain text then no format should be applied;e.g. If A1 to D1 are blank format cells red, when text is entered into B1 remove formatting. All help greatly recieved. Simon. . |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional format rage based on entries within range
Thanks again Don.
I have had a practice with the code you posted, but I've realised that it's not covering what I'm trying to achieve, and because I haven't the first idea regarding code language I can't make any adjustments - I can't even make a colour change as I don't know what colourindex relates to which colour. You mentioned adding blocks, but I'm afraid I don't know what sections constitute a block. Also, perhaps more pertinant than me not understanding the code; the data will be entered into the cells of a row, and there are likely to be 30 or 40 rows per sheet per week. Using the code, would I need to identify each row individually within the code? One last thing, when I enter the code and return to view the spreadsheet it remains unformatted until I have entered and then deleted data in the range. When I use this 'live' I will be opening the template spreadsheet (with the code already in it) then pasting in data from a data registry. Having practiced this, the spreadsheet remains unformatted unless I delete something. Agian, having never used code before I don't know how to make the formatting active from the outset (i.e, the range is red as soon as I open the template, and remains red until / unless data is pasted in). I'm grateful for the time you have spent on this Don, but am not sure that a code route is for me, as I am so out of my depth I don't even know what questions to ask to help myself, and I can't keep relying on guys like you to take the time to reply to each little niggle that crops up as I go along. I was hoping that there would be a quick CF formula that I could enter into a cell then drag along the cells I want affected, followed by a double click on the bottom rt corner to send the formula all the way down the columns - I may just be out of luck! Thanks again. Simon. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional format rage based on entries within range
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software "Simon" wrote in message ... Thanks again Don. I have had a practice with the code you posted, but I've realised that it's not covering what I'm trying to achieve, and because I haven't the first idea regarding code language I can't make any adjustments - I can't even make a colour change as I don't know what colourindex relates to which colour. You mentioned adding blocks, but I'm afraid I don't know what sections constitute a block. Also, perhaps more pertinant than me not understanding the code; the data will be entered into the cells of a row, and there are likely to be 30 or 40 rows per sheet per week. Using the code, would I need to identify each row individually within the code? One last thing, when I enter the code and return to view the spreadsheet it remains unformatted until I have entered and then deleted data in the range. When I use this 'live' I will be opening the template spreadsheet (with the code already in it) then pasting in data from a data registry. Having practiced this, the spreadsheet remains unformatted unless I delete something. Agian, having never used code before I don't know how to make the formatting active from the outset (i.e, the range is red as soon as I open the template, and remains red until / unless data is pasted in). I'm grateful for the time you have spent on this Don, but am not sure that a code route is for me, as I am so out of my depth I don't even know what questions to ask to help myself, and I can't keep relying on guys like you to take the time to reply to each little niggle that crops up as I go along. I was hoping that there would be a quick CF formula that I could enter into a cell then drag along the cells I want affected, followed by a double click on the bottom rt corner to send the formula all the way down the columns - I may just be out of luck! Thanks again. Simon. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Format for one cell based on range | Excel Worksheet Functions | |||
How to conditional format columns in a range based on | Excel Worksheet Functions | |||
Conditional format a cell based on another range of cells | Excel Worksheet Functions | |||
Conditional Format based on range | Excel Discussion (Misc queries) | |||
Need help with a formula for calculating based on a rage of dates | Excel Discussion (Misc queries) |