Flag row if cell values = something specific

Let's say A1 = top
and B1 = Bottom

in C1, I want to say that if A1 = top and B1 = bottom then the cell 
background color of C1 should be red.

How can I do this? 


0
sdatt (10)
5/23/2006 9:36:42 PM
excel 39879 articles. 2 followers. Follow

5 Replies
1518 Views

Similar Articles

[PageSpeed] 8

And I need to do this in a macro. I can't use conditional formating from the 
menu.
I have a recorded macro that does all my formatting and I need to add this.

"Some Dude" <sdatt@myplace.com> wrote in message 
news:e9kw7DrfGHA.4864@TK2MSFTNGP05.phx.gbl...
> Let's say A1 = top
> and B1 = Bottom
>
> in C1, I want to say that if A1 = top and B1 = bottom then the cell 
> background color of C1 should be red.
>
> How can I do this?
> 


0
sdatt (10)
5/23/2006 9:42:18 PM
'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "A:B"

    On Error GoTo ws_exit:
    Application.EnableEvents = False
    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
        With Target
            If Me.Cells(.Row, "A") = "top" And Me.Cells(.Row, "B") =
"bottom" Then
                Me.Cells(.Row, "C").Interior.ColorIndex = 3
            End If
        End With
    End If

ws_exit:
    Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.





-- 
 HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Some Dude" <sdatt@myplace.com> wrote in message
news:e9kw7DrfGHA.4864@TK2MSFTNGP05.phx.gbl...
> Let's say A1 = top
> and B1 = Bottom
>
> in C1, I want to say that if A1 = top and B1 = bottom then the cell
> background color of C1 should be red.
>
> How can I do this?
>
>


0
5/23/2006 9:55:03 PM
What is Me?


pardon the grammar.
-- 
Gary''s Student


"Bob Phillips" wrote:

> '-----------------------------------------------------------------
> Private Sub Worksheet_Change(ByVal Target As Range)
> '-----------------------------------------------------------------
> Const WS_RANGE As String = "A:B"
> 
>     On Error GoTo ws_exit:
>     Application.EnableEvents = False
>     If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
>         With Target
>             If Me.Cells(.Row, "A") = "top" And Me.Cells(.Row, "B") =
> "bottom" Then
>                 Me.Cells(.Row, "C").Interior.ColorIndex = 3
>             End If
>         End With
>     End If
> 
> ws_exit:
>     Application.EnableEvents = True
> End Sub
> 
> 'This is worksheet event code, which means that it needs to be
> 'placed in the appropriate worksheet code module, not a standard
> 'code module. To do this, right-click on the sheet tab, select
> 'the View Code option from the menu, and paste the code in.
> 
> 
> 
> 
> 
> -- 
>  HTH
> 
> Bob Phillips
> 
> (remove xxx from email address if mailing direct)
> 
> "Some Dude" <sdatt@myplace.com> wrote in message
> news:e9kw7DrfGHA.4864@TK2MSFTNGP05.phx.gbl...
> > Let's say A1 = top
> > and B1 = Bottom
> >
> > in C1, I want to say that if A1 = top and B1 = bottom then the cell
> > background color of C1 should be red.
> >
> > How can I do this?
> >
> >
> 
> 
> 
0
GarysStudent (1572)
5/24/2006 12:00:01 PM
Me refers to the containing object, the worksheet the code is within in this
case. From a userform, it refers to the form.

-- 
 HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in message
news:456FEEC6-EBED-42BB-AA5C-BFA8A106CC7B@microsoft.com...
> What is Me?
>
>
> pardon the grammar.
> -- 
> Gary''s Student
>
>
> "Bob Phillips" wrote:
>
> > '-----------------------------------------------------------------
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > '-----------------------------------------------------------------
> > Const WS_RANGE As String = "A:B"
> >
> >     On Error GoTo ws_exit:
> >     Application.EnableEvents = False
> >     If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
> >         With Target
> >             If Me.Cells(.Row, "A") = "top" And Me.Cells(.Row, "B") =
> > "bottom" Then
> >                 Me.Cells(.Row, "C").Interior.ColorIndex = 3
> >             End If
> >         End With
> >     End If
> >
> > ws_exit:
> >     Application.EnableEvents = True
> > End Sub
> >
> > 'This is worksheet event code, which means that it needs to be
> > 'placed in the appropriate worksheet code module, not a standard
> > 'code module. To do this, right-click on the sheet tab, select
> > 'the View Code option from the menu, and paste the code in.
> >
> >
> >
> >
> >
> > -- 
> >  HTH
> >
> > Bob Phillips
> >
> > (remove xxx from email address if mailing direct)
> >
> > "Some Dude" <sdatt@myplace.com> wrote in message
> > news:e9kw7DrfGHA.4864@TK2MSFTNGP05.phx.gbl...
> > > Let's say A1 = top
> > > and B1 = Bottom
> > >
> > > in C1, I want to say that if A1 = top and B1 = bottom then the cell
> > > background color of C1 should be red.
> > >
> > > How can I do this?
> > >
> > >
> >
> >
> >


0
5/24/2006 12:12:18 PM
Thank you
-- 
Gary''s Student


"Bob Phillips" wrote:

> Me refers to the containing object, the worksheet the code is within in this
> case. From a userform, it refers to the form.
> 
> -- 
>  HTH
> 
> Bob Phillips
> 
> (remove xxx from email address if mailing direct)
> 
> "Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in message
> news:456FEEC6-EBED-42BB-AA5C-BFA8A106CC7B@microsoft.com...
> > What is Me?
> >
> >
> > pardon the grammar.
> > -- 
> > Gary''s Student
> >
> >
> > "Bob Phillips" wrote:
> >
> > > '-----------------------------------------------------------------
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > '-----------------------------------------------------------------
> > > Const WS_RANGE As String = "A:B"
> > >
> > >     On Error GoTo ws_exit:
> > >     Application.EnableEvents = False
> > >     If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
> > >         With Target
> > >             If Me.Cells(.Row, "A") = "top" And Me.Cells(.Row, "B") =
> > > "bottom" Then
> > >                 Me.Cells(.Row, "C").Interior.ColorIndex = 3
> > >             End If
> > >         End With
> > >     End If
> > >
> > > ws_exit:
> > >     Application.EnableEvents = True
> > > End Sub
> > >
> > > 'This is worksheet event code, which means that it needs to be
> > > 'placed in the appropriate worksheet code module, not a standard
> > > 'code module. To do this, right-click on the sheet tab, select
> > > 'the View Code option from the menu, and paste the code in.
> > >
> > >
> > >
> > >
> > >
> > > -- 
> > >  HTH
> > >
> > > Bob Phillips
> > >
> > > (remove xxx from email address if mailing direct)
> > >
> > > "Some Dude" <sdatt@myplace.com> wrote in message
> > > news:e9kw7DrfGHA.4864@TK2MSFTNGP05.phx.gbl...
> > > > Let's say A1 = top
> > > > and B1 = Bottom
> > > >
> > > > in C1, I want to say that if A1 = top and B1 = bottom then the cell
> > > > background color of C1 should be red.
> > > >
> > > > How can I do this?
> > > >
> > > >
> > >
> > >
> > >
> 
> 
> 
0
GarysStudent (1572)
5/24/2006 12:38:02 PM
Reply:

Similar Artilces:

Setting series values on Excel Chart
Hi I'm struggling very hard in getting beyond this error when trying to use VB to generate a chart from data previously inserted into the sheet. Unable to set the Values property of the Series class. The code works absolutely fine in XL2007 but I am having to port it back to 2003 and the inference is that its empty values that cause it to hiccup - something I cannot avoid. My code is: 1. Retrieve selected data from database and place it at the top of the sheet 2. Add The Chart 3. Iterate through the data (amount can be variable depending upon user inputs) For j as i...

Convert single colum/multiple rows to multiple colums.
Hi, I have a .dat file when opened with Excel it has 1 column and 7 rows per entry. I would like to delete some rows and convert the rest to something like. Any chance this can be done. It's quite large. 51,793 rows. TIA Jeff Col 1 Col 2 Col 3 Name Date Lenny Kravitz - 2000 - Greatest Hits -- Table: {2} { "music" "Name", "06/04/2008", "Lenny Kravitz - 2000 - Greatest Hits", }, --Table: {3} etc. etc. ...

Timestamp value of GINA window presentation
I am currently troubleshooting slow logon times for a client with computer infrastructure spread across the globe. Before I start invesagating I want to create a baseline for logon times. I have modified the clients logon script to record when it starts and when it ends. I am using the computers tick count to record how long the computer has been on, but realize that there is a space of time between the user authenicating to the computer (control-alt-delete) and when the logon script starts, if the customer turns his computer on and then leave for an extended period of time I ...

data value in Form field if no table entry
I have a form with a field which pulls through and concentenates 2 fields called [ContactFirstname] and [ContactLastName]from my table There are however some customers for whom I do not have names and therefore instead I would like Sir/Madam to appear in the field in the form I think I have seen this done somewhere using ELSE? but can't find it Any help/ideas gratefully received Perhaps something like this: Nz(Trim([ContactFirstname] & " " + [ContactLastName]), "Sir/Madam") -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access use...

how to query my web site from VBA and return a value to VBA
Hello All, From VBA I would like send a value to my web site, and have it return a value. I've learned how to use FollowHyperlink to send a value to an ASP script, but how can the ASP script send a value back to VBA?? Thanks, Brian Austin, TX You can use xmlhttp to make a request to your web page: '********************************************************* Sub Tester() MsgBox WebResponse("http://www.mydomain.com/myactualpage.asp? info=3Dblah") End Sub Private Function WebResponse(sURL As String) As String Dim XmlHttpRequest As Object Se...

Summing distinct rows in same cell
Sorry for the bad title I'm not sure hte best way to sum up this issue. Fruit Price Total Apples 0.69 40 Bananas 0.34 38 Lemons 0.55 15 Oranges 0.25 25 Apples 0.5 10 Pears 0.59 40 Almonds 2.8 10 Cashews 3.55 16 Peanuts 1.25 20 Walnuts 1.75 12 Apples 0.5 5 Given the data above I'm trying to sum the totals for only the Appl rows. So basically I need to search the Fruit column find the row that have "Apples" and then sum their corresponding Totals, giving m 55. I've been playing with this for hours with Lookups and Indexes bu am not really getting anywhere. Also, I can...

Save As using contents of Cell in Name
Can someone help with code that changes the "save As" file name to combination of the original File Name + the contents of a cell, an saves file in same folder as the original. E.G. Original File Name= Timesheet Cells B1= Location Cell C1 = Date in format 3-5-2004 Cell A1 = concatenate(B1,C1) Fle Name will therefore be "Timesheet Location 3-5-2004" The other problem is with the date. When concatenating, how do I forma the date to avoid it looking like "Location 38051" Any help much appreciated Nic -- Message posted from http://www.ExcelForum.com Hi for c...

How can I choose alternate rows in a column?
My requirement is to be able to create a column whose elements consist of difference between adjacent elements in a column, say column A. If I can choose alternate elements and create 2 new columns then I can just subtract the 2 columns easily. Huh? "pnair" <pnair@discussions.microsoft.com> wrote in message news:D47AD012-084B-49C6-8672-5067E8455D9E@microsoft.com... > My requirement is to be able to create a column whose elements consist of > difference between adjacent elements in a column, say column A. If I can > choose alternate elements and create 2 new colum...

Assign values for one column to another.
Hi I have in column T certain numbers and texts that that I require to assign a value to as below, in the adjacent column. Again any pointers would be much appreciated. Kind Regards Celticshadow T U 1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8 9 9 0 10 F 10 UR 10 U 10 R 10 S 10 L 10 P 10 PU 10 BD 10 D 10 Well, imagine that two-column table occupies cells Y1:Z20. Put this formula in U1: =3DVLOOKUP(T1,Y$1,Z$20,2,0) and copy down. Hope this helps. Pete On Oct 14, 4:26=A0pm, Celticshadow <Celticsha...@discussions.microsoft.com> wrote: > Hi > >...

Count on cell for each time it is changed
I would like to count the number of times a cell has been amended/ changed e.g cell b2 has the word red it it, in cell c2 i would like a counter for everytime the cell in b2 has changed, at the end of the day you get final number. Is this at all possible? Jelinek, You can do it by putting the following VBA macro in your sheet: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$2" Then Cells(2, 3) = Cells(2, 3) + 1 End If End Sub Art "Jelinek" wrote: > I would like to count the number of times a cell has bee...

Excel formulation to automate values
Hi there, I have a little problem on arranging a small multi-worksheet excel here. Let me explain in a short way then give some details on it. I am trying to make an offers recordsheet which has two worksheets in it. One for products(and their base prices) and an offer sheet. I would like to use a bit dynamic data here. So when I type the name of the product excel would give me the price from other worksheet. in details; Prices worksheet is something like: A B 1 Product BasePrice 2 mouse 5 3 keyboard 8 4 ... And the ...

Cells Fill Automatically on Another Workbook
I've created what we'll call a seed worksheet to be used over and over for different clients. I have linked its cells to another workbook. As the originating seed worksheet directs its cell data to a specific cell on another workbook, how can I accomplish the workbook data not being overwritten but the new incoming data default to the next unused cell in the column? i.e. If the original seed worksheet cell B1 links to the worksheet cell A1, I would like the next instance of creating a new customer and his B1 information on his use of the seed worksheet to populate onto th...

hide/change color of selected row headings
Is it possible to hide the row heading numbers for selected rows (i.e. rows 51 and greater) for just the selected sheet? I'd like to have a color with no row heading number appear that matches the fill color I select for the adjacent cells. Secondly, any ideas as to how I can prevent the user from scolling further down than a certain point (i.e. row 51). Thanks in advance. Joe Row headings are either on or off, you can't hide some. However, you can achieve a similar effect by hiding *all* headings (Tools/Options/General) and putting the numbers 1:51 in A1:A51. to limit scro...

Concatenating cells but excluding blanks
Hello, I am trying to create a result field, concatenating populated cells from the previous 12 columns on that line, but excluding blank cells and putting a * delimiting character between each instance - please find below a 4 column example. ID 1 2 3 4 Result Z A C D A*C*D Y B C B*C X A B D A*B*D Each of the 10,000 lines of the spreadsheet is different - there are at least 5 blank cells on each line Any help gratefully received. I am working in Excel 2007 Many thanks. Bob Try this: http://img690.imageshack.us/img690/5826/nonamee.png Micky "Bob Fr...

Can't insert rows
I am using Excel 2007 and have just loaded a spreadsheet created in a previous version which is running in Compatibility mode. When I try to insert a row I get an message "Cannot shift objects off sheet". How do I insert rows on this sheet? No problem with another workbook loaded at the same time which was created in 2007. See if this helps http://support.microsoft.com/default.aspx?kbid=211769 "Cannot shift objects off sheet" error message when you hide columns in Excel -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my...

Site-specific config items
I've got an ASP.NET 3.5 website on a shared-hosting server, and of course my own development copy. Data is in a SQL Server database. I've looked at some of the strategies available to configure the connections string appropriately for each site without accidentally overwriting one with the other. One of them, to define the data connection string in machine.config, is unavailable to me on the production website, but I was thinking about doing it this way: Suppose my site is as follows Root --App1 --App2 I have two ASP.NET applications, and I don't have anything d...

Copy Data from One Group of Cells to Another Group
I have five columns of data on two different sheets in the same workbook. One set of columns is sorted in ascending date order the other in descending date order. When I enter data into the last row of Sheet 1, I need the data in that row in columns A, B, C and D to be copied into Sheet 2 columns A, C, D and E in a newly inserted row 14. Is this possible with the use of a macro? I can find the last cell in Sheet 1, but then need to go up one row and back to column A. I am having difficulty with that. Thanks is advance for any assistance offered! /s/ Alan Auerbach On Sat, 26 May 2007, ...

Copy cell contents, then paste into the same cell with other text.
Hi! I tried a search first and couldn't find anything like this. My spreadsheet has a column for shipping that takes a series like this for each product: ?0.0*0.13.2*d*0x0x0:07:24:04 Following the question mark is the handling charge (0.0 in this example). This is followed by an * and then the weight of the item (0.13.2 in this example which is 13.2 ounces) I have a list of product weights in a colum with just pounds and ounces. I need to copy that information, then paste it into the weight area of the string above and then paste those modified contents back into t...

How to select other random cells
I have a 2 part question. I want to have a 1 question survey randomly filled out by 200 people. Column A lists the 1 questions. Columun b has ABCDE listed in cells B6 to b10. I would like 200 samples in columns c, d, e, etc. How do I set this up so that they randomly Coose B6 to B 10. Second part. On another spreadsheet, I have a similar situation, but the user has 10 answers to select from and I want them to randomly select all that apply. How would I do this? Thanks! ...

How do I get total value data labels in a stacked bar chart?
I have a 3-D stacked bar chart with four series and I want to have the total value in each category be displayed in a data label. Can I do this, and if so, how?? Hi, This should help http://www.andypope.info/charts/StackColTotal.htm Cheers Andy blemerson wrote: > I have a 3-D stacked bar chart with four series and I want to have the total > value in each category be displayed in a data label. Can I do this, and if > so, how?? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info Thanks, Andy. The CFO is thrilled. Heather "Andy Pope" wrote: > Hi, >...

Number Rows on a report?
It would be anice addition if I could add numbers (a count) in front of each row on my filtered report. Any easy way to do this. I was thinking an unbound text box with something in the control source - I just don't know the something :) Any help here will be appreciated. Thanks in advance On Wed, 20 Jun 2007 14:07:27 -0700, Dave wrote: > It would be anice addition if I could add numbers (a count) in front of each > row on my filtered report. > > Any easy way to do this. > > I was thinking an unbound text box with something in the control source - I > just...

Outlook 2003 Followup Flags
I am running Outlook 2003 and everyone else in the office is currently using Outlook 97. Whenever someone sends me an e-mail with a follow-up flag attached and I forward that e-mail I lose the follow-up flag. In Outlook 97 and in Outlook 2002 this was never a problem. I would hit forward and the follow up flag and all attachments would stay as normal. I have looked everywhere. Is there a setting or something that I am missing to correct this? We use follow-up flags extensively in our office. We really need a fix for this. Thank you. ...

How do I call individual cell data from an Excel sheet into Powerpoint or Word?
Ok, I have an Excel work sheet done up to calculate discounts given to employees based on their employer. I now need to make a flyer in either Word or PowerPoint (or another program if needed) but I'd like for it to pull the data from individual cells in the Excel sheet. For instance in the midst of the graphics and flyer text that will remain the same for every flyer I'd like to be able to tell it to refer to Excel document, and then pull all the information for say, the company in row 7 (since 7 is the first company listed) It would need to pull the text (co. name) from A7, sug. r...

Cell padding
I have a worksheet with multiple row heights. When I do autofit some of the words are being cutoff in the rows. How can I adjust to a little extra space in each cell without manually adjusting each row? I'm assuming that the words are being cut off from left to right (not top to bottom) - I am not sure from you posting. If the above is the case, in EXCEL 2007, try the following:- - click in the cell to the left of column A and above row 1 - this will highlight the whole Worksheet - then double click the line between the A and the B column headings The above wi...

Copy cell to a new cell #2
In Column B I have certain values which start with GB2 e.g GB2-02210. I would to like to copy these values and paste it in another cell. I would like to do this with only values which start with GB2. Many thanks -- Message posted via http://www.officekb.com I would use Data>Filter>Autofilter and use custom>begins with... -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England www.nickhodge.co.uk nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "mohd21uk via OfficeKB.com" <u20517@uwe> wrote in message news:5ede27c5beb13@uwe... > In Column B I have certain values ...