Change a cell's fill color dynamically?

Is it possible to lookup a value in a cell over here and change a cell's fill 
color over there based on certain criteria?

For instance, if all the workdays for a month are listed in column A, is it 
possible to look up all the Fridays and change the corresponding cell in 
Column C from whatever color to Yellow?  

While I'm at it, is it possible to unlock those certain C cells for editing, 
as well?

Thank You so very much.

Arlen
0
Arlen (19)
1/22/2005 7:25:06 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
615 Views

Similar Articles

[PageSpeed] 12

You can handle the color issue with conditional formatting; you don't need any
lookup function. 

As far as locking/unlocking the cells, you probably need to do that with VBA
code. But Fridays are Fridays and won't change. Why do you need to change the
locking?

On Sat, 22 Jan 2005 11:25:06 -0800, "Arlen" <Arlen@discussions.microsoft.com>
wrote:

>Is it possible to lookup a value in a cell over here and change a cell's fill
>color over there based on certain criteria?
>
>For instance, if all the workdays for a month are listed in column A, is it 
>possible to look up all the Fridays and change the corresponding cell in 
>Column C from whatever color to Yellow?  
>
>While I'm at it, is it possible to unlock those certain C cells for editing, 
>as well?
>
>Thank You so very much.
>
>Arlen

0
anonymous (74722)
1/22/2005 7:47:53 PM
=?Utf-8?B?QXJsZW4=?= wrote

> For instance, if all the workdays for a month are listed in column A,
> is it possible to look up all the Fridays and change the corresponding
> cell in Column C from whatever color to Yellow?

You could use Conditional Formatting (you would have to Unprotect the sheet 
first):
Say dates in A start at A2
Select all corresponding cells in C
Format|Conditional Formatting
Formula Is|=Weekday(A2)=6
Format|Patterns and pick Yellow

-- 
David
0
1/22/2005 9:51:38 PM
Reply:

Similar Artilces:

autofill cells without a pattern?
Hi! I wonder if someone could give me a hint of how to auto fill cells or rows when they don't have a pattern. Every month I pull data with 3000+ entries in CSV format. Each cell follows a row order (eg. Row1=Group, Row2=Term, Row3=SE, etc.), however, when data on cell A1 is the same as B1, and then B1 comes empty. Also, if data on A1 is same as B1, but data on A2 is different than B2, then A2 and B2 have their corresponding data but B1 is still empty. Until now, I have selected the empty cells (one by one) and performed a "copy down" (ctrl+D) but I am getting tired of that (more...

Code to Implement Required Cells Not Working
I have a worksheet called "Change Request Form" where users are required to input values in the following cells: C9 = CPM Full Name C10 = IT PM Full Name C11 = Change Type C12 = Reason Category C13 = Project Name C14 = Release C15 = PAT ID C16 = PRISM ID C17 = Explanation E15 = New PAT ID E16 = New PRISM ID I want to require users to provide input in most or all of the other aforementioned cells prior to Saving the workbook depending on the value of cell C11. I wrote the code shown below and put it in the ThisWorkbook object. Unfortunately, upon testing, u...

How to combine 2 cells' text into 1 cell
Hi all, Assume Cell B1 is "ClassA" Assume Cell B2 is "MayMay" I would like to make D2 as "ClassA MayMay" How can i do that? I have tried to write as =B1,B2 it seems not workable~ Thanks. =B1&" "&B2 -- Regards, Peo Sjoblom (No private emails please) <athatisme4@gmail.com> wrote in message news:1129074458.845268.98090@o13g2000cwo.googlegroups.com... > Hi all, > > Assume Cell B1 is "ClassA" > Assume Cell B2 is "MayMay" > > I would like to make D2 as "ClassA MayMay" > > > H...

how do I copy only visble cells in office10
i can't remember how this is done in this version Click Go to then special and "visible cells only" and then CTRL+C (copy) Click yes if helped -- Greatly appreciated Eva "Tantalus24" wrote: > i can't remember how this is done in this version Ctrl+g > Special > Visible Cells Only > OK "Eva" wrote: > Click Go to then special and "visible cells only" and then CTRL+C (copy) > > Click yes if helped > -- > > Greatly appreciated > Eva > > > "Tantalus24" wrote:...

I cannot get excell to change to landscape
I want to print in landscape format. Yes I know the way to change it - File page set up page landscape but that does not work. Even when landscape is "clicked" the page still prints in portrait. When I do a page preview it appears in portrait. Any ideas on another "switch" or setting. I have closed excell and restarted but still the same thing. Thank you James Almost always, printing issues in Excel are directly tied to the printer being used. Is your printer capable of Landscape? Does it have some settings that need to be changed (Windows Start>Settings&...

recognizing cell.interior.colorindex
I adapted Pearson's code to write a macro to test if a cell had a colorindex>0, then (if so) remove all conditional formatting that colored the cell (leaving the color). It works fine on a small test spreadsheet with different test conditional formats. When I try to run my "real" application, it always returns -4142 (clear) for cell.interior.colorindex, regardless of the cell color. It doesn't seem related to Pearson's code. Any help appreciated. TIA -- Bill Roberts Chip has lots of code on his site. I don't know what you used to check. But test...

Keep data that is entered in a field until changed
Access 2000, I want to retain a field contents that is entered, until it is changed. Like a default, except that it will only change whenever the user changes the field contents. How do I accomplish that? "lths-kblank" wrote: > Access 2000, I want to retain a field contents that is entered, until it is > changed. Like a default, except that it will only change whenever the user > changes the field contents. How do I accomplish that? If you set a default value for a field, the field will retain that value. If you change the value, the field will save the change. ...

Dynamic Access Report from filtered Form
I need help. I have succuess fully applied the "how to create a dynamic crosstab report in access" from instructions off of microsofts web site to my own report and query. My problem is this. I have a form in datasheet view, based off a query. When the form is open I can hide / unhid columns as needed and apply filter by form. That all is great. But now I want to beable to send the filtered data on the datasheet form to the Dynamic Report I created showing only the filtered records and also only show the columns that I did not have hidden on my form. How do I do this? I have t...

excel mouse pointer changed to "unavailable"
Does anyone know why my mouse pointer could have changed from the regular cross to the "unavailable" pointer? (It's a white circle with a black border and a black line running through it diagonally.) It changes from the regular pointer when I am over a cell to the unavailable pointer when I hover over a chart. This happened suddenly when I was updating several charts in a row. Thank you! Submitted via EggHeadCafe - Software Developer Portal of Choice VALIDATE MAC ADDRESS (REGEX) http://www.eggheadcafe.com/tutorials/aspnet/562f1fa4-63dd-498a-b3f3-53f9f92129af/validate-mac-...

Name a worksheet tab with data in a cell
I have Excel 2002 and would like to rename a worksheet tab with a cell reference. Example, name the tab "John Doe" his name is located in cell b2. each time I change the value in b2, the name of the tab will change accordingly. Hi Donovan, In the worksheet's module (right-click the sheet's tab | View Code), paste the following: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("B2")) Is Nothing Then Me.Name = Target.Value End If End Sub --- Regards, Norman "donovan" <anonymous@discussions...

average of non blank cells
i have read somewhere that it is possible to take an average of non blank cells....is this correct....if so how!! thanks in advance Robert. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ "robert_woodie" <robert_woodie.wgt6m@excelforum-nospam.com> wrote in message news:robert_woodie.wgt6m@excelforum-nospam.com... > > i have read somewhere that it is possible to take an average of non > blank cells....is this correct....if so how!! > > th...

automatically add borders to a cell after entry is complete
I've seen this done before. I would like to format the cells in my spreadsheet to automatically add a border to seperate one cell entry from the next after I'm finished with the entry. In other words after I tab out of the cell. Thanks, BKD you can set a conditional formatting. Highlight the area you will input numbers go to format--> conditional Formatting--> cell value is change to not equal to 0 click on format, select border--> choose one with all borders around it, click ok and ok again. now if you input any number except 0 you will see the borders. hope it hel...

Help: Seting the value of another cell with a formula
I know that there's a way to make a cell a particular value based on the entries of a range, or array of cells, but is it possible to do the reverse, using only one formula in a cell. Here's what I'm trying to do: What I would like to do is set one of a range of cell to have a value based on the value in A2. Example: Cell A1 has a value of 2, A2 has a value of 2007. I want A10 to equal A1, i.e.:2 Cell A1 has a value of 2, A2 has a value of 2008. I want A11 to equal A1, i.e.:2 Cell A1 has a value of 2, A2 has a value of 2009. I want A12 to equal...

Prevent excel from changing numeric data ranges into dates?
I want to prevent excel from changing my numeric data into dates (ie. 10-12, into 12-Oct). I want to import specific data ranges, not a date values. Whenever I paste or type a possible date, like 10-12, excel assumes I am writing Oct. 12, 2004. Then, Excel formats the cell into a Custom d-mmm format (see scrn shot @ www.vd4.org/excel_issue.gif). If I change the Custom format to General, excel converts the date into the DATE'S value (ie. 12-Oct, into 38272; which I think is the # of days since 1900?). How can I *prevent* excel from changing my data ranges into dates (ie keep 10-12 ...

Two different cells
I have a file with more the 1000 names and addresses. My proble is that the firat and last names are in 2 differents cells. I i would like to put it in teh same cell. Is there anyway I csn do that? Please help too many names to re-type Carlos Assuming that Column A contains the first name and Column B the last name... =A1&" "&B1 OR =B1&", "&A1 ....depending on your preference. Hope this helps! In article <520FA0CA-1940-42E5-B84D-22BA15D59638@microsoft.com>, "Hombreck" <Hombreck@discussions.microsoft.com> wrote: > I have a...

Conditional Formatting
Hi I need some help with conditional formatting. I'm trying to highlight those cells which have a plus sign in them. Can someone help with the formula to enter into the conditional formatting dialogue? Grateful for any assistance. Best Wishes Hi Colin if the Plus sign + is part of a text string Select the column range first then =FIND("+",A2:$A$160) change the range to your needs. If the + sign is alone in the cell, you don't need a formula, just select > Cell value is > then select Equal to> and type the plus sign in the third box. H...

Changes Since Moving to Exchange
Hi there; I have subfolders within the inbox, and then subfolders within the subfolders. I also have rules so that when a message comes in from a certain person the message goes into their folder. Before we moved to our new exchange server if someone sent a message and the message went to their folder, the number of unread emails in the folder changes (ie, if Joe Bloggs sends an email to me his message goes to the Joe Bloggs folder, and it looks like Joe Bloggs (1)). And the folder that the 'Joe Bloggs' is in (for this example it could be 'staff') would also...

Moving from cell to cell
Moving from cell to cell using arrow key stopped working. Help! Maybe hit Scroll Lock key... If Scroll Lock is on, the arrows still work, but in a different way. HTH Regards, Howard "crys0814" <crys0814@discussions.microsoft.com> wrote in message news:1B985BCA-BFB8-4718-8ED8-833BE78D8895@microsoft.com... > Moving from cell to cell using arrow key stopped working. Help! Hi there cry0814, It is possible that you have accidentally pressed your scroll lock key which disables your arrow keys. Try pressing it again and let us know your results Regard...

Change from SBS 2003 to SBS 2003 R2
Does anyone know if the license from Windows SBS 2003 works on R2 Edition? Here's my situation: I have an old server running Win SBS 2003. This is a new client and i just found the installation to be all screwed up (they are not even using Exchange, the domain is not .local, I cant find the SBS management panel, etc ) and barring any magic solution that allows me to improve on the current install, what i really want is to start from scratch, probably on different hardware (new server). So I whas thinking to my buttons: During reinstallation, can I now use the latest ver...

access a cell in a range
I have variables: dim myrange as Range dim myString as String myrange specifies a specific column. How can I specify the cell in row 1 of myRange so I can set it's contents to myString? Hi there keyser soze, (Fan of the movie, eh? ;) <g> ) Like so .. myString = myRange(1).Value HTH -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) <keyser_Soze@usa.com> wrote in message news:1128697509.320303.12550@g49g2000cwa.googlegroups.com... >I have variables: > > dim myrange as Range > dim myString as String > > myrange specifies a specific ...

How to change the color of CComboBoxEx
I have derived my class from CComboBoxEx and I change the colors (SetBkColor and SetTextColor) in OnCtlColor() in response to CTLCOLOR_EDIT and CTLCOLOR_LISTBOX; I also return my brush; As a result the colors of the edit part of the combo do change but in the listbox only the space around the text is painted. The text itself and its backgound remain as the Windows defaults. "David A. Mair" <mairda@hotrmail.com> wrote in message news:<ObKF5Q#bDHA.2632@TK2MSFTNGP12.phx.gbl>... > I have it working OK, here's my CColorComboBox::OnCtlColor(): > > HBRUSH CColorCo...

Averaging cell's...problems with Div/0
Hi guys. First time poster here so be gentle with me. I am looking fo some assistance averaging a range of 1 to 3 numbers. Here is what I have so far. =(D3+F3+H3)/(IF(C3=0,0,1)+IF(E3=0,0,1)+IF(G3=0,0,1)) This works great. What it does is checks to see if there is a value i the cell, then counts it and divides by the right number. I.E if yo only have two values out of 3 filled in it divides the number by tw instead of 3. My problem... if all 3 fields are 0 then I get a divide by 0 error. Any suggestion on how to fix this? I don't want my spreadsheet to loo messy before I start plu...

How do you change a field name in 2002 Excel
I have copied and pasted a whole database from Works into the Excel program but can't seem to find a way to change the field names from A, B, C, to what I want as Last Names, First Names, etc. Aarrrrgh. It can't be THAT difficult! <G> Using the HELP did nothing for me thus I am here asking this silly Q. AnnE in MN You cannot change the Column letters from A, B, C etc. You can choose to have column and row headers not shown under Tools>Options>View Enter your titles(names) in row 1 then select A2 and Window>Freeze Panes to lock row 1 in view. Gord Di...

Highlighting A Cell
Does anyone know how to do a formula that says if a cell equals the wor FALSE then highlight this particular cell gray? Example: Typing in Cell A2 IF A1 = FALSE then gray otherwise white I tried to do it in Conditioning Formatting but can't figure it out t reference another cell. Any help would be greatly appreciated -- Coltsfa ----------------------------------------------------------------------- Coltsfan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1998 View this thread: http://www.excelforum.com/showthread.php?threadid=46898 Good evening Colt...

Calculating values for empty cells.
Hello. I have a very simple problem that I cannot find the answer to. I have data in two columns, some of the data in one of the columns is missing and I want to automatically extrapolate what the data should be based on the trend. How can I get Excel to fill in empty values without overwriting the known values. Below is a sample of my data. 1500 1600 1700 1800 4000 1887 5700 1900 5500 1910 7300 1912 8100 1920 8800 1926 10100 1930 11900 1936 12200 1938 -- Ryan Taylor rtaylor@stgeorgeconsulting.com Not sure what yo...