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 39880 articles. 2 followers. Follow

5 Replies
586 Views

Similar Articles

[PageSpeed] 28

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:

Keyboard Macro Just Copies Content of Previous Cell
I created a simple keyboard macro to edit the contents of a cell by simply inserting a "b" in a particular position. For example, I have a series of alphanumeric numbers that start with S1253, e.g., S12536675. I wanted to insert a "b" after the first five digits on the left to revise the series of alphanumeric numbers to start with S1253b, e.g..S1253b6675. However, after creating the keyboard macro, when I run the macro on the next cell to be edited (i.e., S12536676), the macro simply copies the contents of the original cell that I edited when I first created the ...

XL2003 format of row- and column headers
Hello, how can I change the font of the headers of rows and columns in a sheet? Thanks for any help Wolfgang On Oct 3, 3:26=A0am, "Dr. Wolfgang Hintze" <w...@snafu.de> wrote: > Hello, > > how can I change the font of the headers of rows and columns in a > sheet? > > Thanks for any help > > Wolfgang Format > Style... > Normal > Modify ...

Flagging
Is there a way to view all flagged items, either completed or incomplete? What version of Outlook? Do you mean items across multiple folders or in a single folder? What kinds of items? -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP http://home.hawaii.rr.com/schorr **I apologize but I am unable to respond to direct requests for assistance. Please post questions and replies here in the newsgroup. Mahalo! "Curt" <anonymous@discussions.microsoft.com> wrote in message news:5d6c01c3ad40$bffcbdf0$a601280a@phx.gbl... > Is there a way to view all flagged items, either > co...

How attach data in a row so it can be sorted by the date column?
I have a four spread sheets all the same that I have copied and pasted into one. I need to know now how to sort the rows by the date column. When I select the date column and hit the tool bar a-z button only the dates sort and not other information in the adjacent columns. What can I do? Hi! Select the entire range that you want to sort. Goto Data>Sort. Sort by the column that contain the dates. Biff >-----Original Message----- >I have a four spread sheets all the same that I have copied and pasted into >one. I need to know now how to sort the rows by the date column. W...

Row Height #3
I'm working on this spreadsheet and we are constantly adding more information to these cells in a certain column. After a while when there is a lot of text added into the cell, it begins to not continue wordwrap-ing it anymore, and cuts it off, as well as when choosing AutoFit for cell height, it won't go any bigger than 171.00. Is there anyway to change those settings? If you add alt-enters to force a new line within the cell, then you'll be able to see more text in that cell and the wordwrap should come back. Try adding them every 80-100 characters. Chantel wrote: > &g...

Trendline values #2
I used the function "LOGEST" to get an exponential fit of a series of numbers, which gives me a value of 1.0610. I also used "RSQ" function to get the R-square, which is 0.9442. Then I made a chart based on the same series of numbers and add trendline to the curve. I selected "Exponential" under "Type" and checked "Display equation on chart" and "Display R-Squared value on chart". The value shown on the chart is "y=3928.8e^0.0592x" and "R^2=0.9104". I expected to see "y=3928.8e^0.0610x" and "R^...

Row selections by row # OR by even/odd rows in another spreadsheet
Is there a way in Excel 2003 to ONLY select ODD numbered rows in a formula/calculation? For example, in Sheet1 I have a person's name or company name that is contained in all odd rows (1, 3, 5, 7....) in column A. I'm creating a formula for Sheet2 that references the odd rows of Sheet, but I don't want to manually change each reference in the formulas for each cell. Row 2 contains phone numbers, city, state etc. The formula is something like "=if(mid(Sheet1!A1,1,1)="(",A1,mid(Sheet1!A1,5,4)) and I have other similar but more complex statements for the other ...

search a matrix for values
OK, this is a bit of a tricky problem.... i have a 7*7 matrix in excel, which is full of numbers... i want to search in only half the matrix, where half is from cell1-1, to cell 7-1 (i.e. top left to bottom right). what i want to search is for numbers a certain value, say X. what i then want to do is tablulate these numbers in one column, and then tablue late their position as a reference to the matrix (i.e. their matrix position). how on earth can i do this? Phil I have assumed that the matrix is in A1:G7 In A10 I entered =INDEX($A$1:$H$7,ROW(A1),ROW(A1)) and copied down to A16 In...

Can Row and Column Inputs in 2 Var. Data Table be on a separate sh #2
I have created a two variable data table that I would like to reference data from a separate worksheet (i.e., the Row and Column Inputs would reference cells on a separate worksheet). How can I accomplish this? When you are in the dialog box asking for reference cells, simply switch to the other worksheet and point to the cells. "RED" wrote: > I have created a two variable data table that I would like to reference data > from a separate worksheet (i.e., the Row and Column Inputs would reference > cells on a separate worksheet). How can I accomplish this? I trie...

Wacky Default Values
I have a picklist in both my Contact and Account records with a default value set. If I create a new Contact or Account, this default value works properly. However, when I convert a Lead to either a Contact or Account, this value appears in the record when it is pulled up, but it is displaying a blank in all my views. Has anyone experienced this situation and/or have any suggestions? ...

Quick Flags and Blackberry
Good Morning, I am an EA and use quick flags (Outlook 2003) to indicate the significance of emails in my boss' account. However because he travels frequently he uses his Blackberry to access emails and can not see the flags. Is there a way to sync the Microsoft quick flags and Blackberry or is there another suggestion for sorting emails by importance that will transfer to a Blackberry? Thank you. -- K you could try turning on Inline editing and type in the Subject field. Regards Judy Gleeson MVP Outlook Trainer and Consultant There are various articles about using Outlook he...

How to write a macro to hide an entire row
Hello guys, I would like to write a macro that would hide an entire row if the cells value in that column is equal to 0. Someting like this: A B C 1 20000 2 0 - if c2 = 0 then hide entire row 3 20000 Thank you for your help. José Hi, Try this Sub Hide_Me() Set sht = Sheets("Sheet1") ' Change to suit lastrow = sht.Cells(Cells.Rows.Count, "C").End(xlUp).Row For x = lastrow To 1 Step -1 If sht.Cells(x, 3).Value <> "" _ And sht.Cells(x, 3).Value = 0 Then sh...

How can I reference the last data item in a column or row?
I want to display or use the last (most recent) data in either a column or row withour specifically accessing the individual cell. Is there a funcion similar to Min() or Max() that can help me or perhaps another way to solve this question? Thanks See: http://www.xldynamic.com/source/xld.LastValue.html Hope this helps Rowan snappy wrote: > I want to display or use the last (most recent) data in either a column > > or row withour specifically accessing the individual cell. Is there a > funcion similar to Min() or Max() that can help me or perhaps another > way to solve th...

difference between times returns a value
I have a table/form which i have 2 columns/fields which time is entered. "DRSR Time" and "Gate Time". What i'm trying to do is return the value 'Hit' (if the difference between the 2 times is less than 60 mins) or 'Miss' (if it is greater than 60 mins), in a 3rd column/field called "adhearance". Any help would be great, Thanks hi Charno, On 05.05.2010 11:33, Charno wrote: > I have a table/form which i have 2 columns/fields which time is entered. > "DRSR Time" and "Gate Time". > What i...

Flagging
I did something stupid and now I am in the soup. My boss's boss called me while he was traveling on business to check his e-mail messages because his assistant was out to lunch. When I opened his mailbox he had literaly 15 of those pesky flagged message pop-up reminders. I dismissed them because I find them annoying and wanted to get to his messages. It turns out those pesky reminders are a valuable mnemonic device for him. How do I restore the active pop-up messages without changing the original due date? He wants the original action date to remain. When I try to put in b...

Duplicating cells with formula's
I'm havnig a problem that I just can't figure out... I have the formula.... =IF(ISNUMBER(VLOOKUP(A2,Sheet3!A3:B280,2,FALSE)),VLOOKUP(A2,Sheet3! A3:B280,2,FALSE),"") I need to copy the formula down a column from row 3 - 280 only changing the A2 after each vlookup to the next corrisponding A3, a4, a5. etc. When I drag the little plus down it copies it but adjusts it incorrectly. Example of how I want it to look.... This formula would go in Column A Starting Row 2 Row 2:=IF(ISNUMBER(VLOOKUP(A2,Sheet3!A3:B280,2,FALSE)),VLOOKUP (A2,Sheet3!A3:B280,2,FALSE),"") Row 3:=...

average a row of numbers?
I am trying to get the average of a row of numbers with various numbers of columns. I have 50 rows of numbers and am looking for the formula to average all of the rows, showing the average of each row. I know how to do them individually, but very time consuming. Any help would be appeciated. Insert a column and enter =AVERAGE(B1:IV1) Copy down 50 rows. Gord Dibben MS Excel MVP On Mon, 6 Aug 2007 16:56:01 -0700, wannabe68 <wannabe68@discussions.microsoft.com> wrote: >I am trying to get the average of a row of numbers with various numbers of >columns. I have 50 rows ...

Conditionally format another cell?
Is there a way to change the background color of a cell based on the contents of a different cell? thanks!!! Just use a formula that refers to the other cell. If I want X99 to change depending on what's in A1. I select X99 and then format|conditional formatting Formula is: =$A$1="anythingyouwant" Robin wrote: > > Is there a way to change the background color of a cell based on the > contents of a different cell? > > thanks!!! -- Dave Peterson ...

FLAGS!
Is it possible when attaching a FLAG (with date and time) to an e-mail to receive an actual alert or reminder to follow up on the due date & time. Thanks for any help/advice Glyn I can answer my own question thanks to Knowledge BASE - maybe of help to others. The e-mail must be in either INBOX, CONTACTS, CALENDAR or TASKS. Outloook doesn't monitor other folders! >-----Original Message----- >Is it possible when attaching a FLAG (with date and time) >to an e-mail to receive an actual alert or reminder to >follow up on the due date & time. >Thanks for any help...

Problem Changing Cell Formating
I have "inherited" a workbook used to generate budget documents. As part of the book, there are several sheets that contain the formatted "form" which includes boxes or borders around certain cells. My problem is I cannot change the format of these cells using the "Format:Cells" menu or the Ctrl+1 shortcut. I can however copy a format to these cells to change them. Unfortunately, if I happen to copy something to the cell that is "detrimental" to my formatting I play the devil getting the formatting corrected. Another twist is I can change all of the form...

Dynamic sorting of rows of data.
Hi, I have data in Column B thru V. Row no 1 is header row. Data extends till lets say row number Z where Z keeps on changing depending on the amount of data. For calculating the value of Z, I use the no of entries in column F as the basis ( as it could be possible that cell B59, C 59 has no data but F59 will have data) This Z is calculated within the VB code and designated as the variable "rowcount". Now comes the real ( and difficult part)... I want to sort the above data. I recorded the macro as per my sorting specifications and got the following code: Rows("2:" ...

Mark as read and Clear Message Flag
I have defined two rules per the work around because of a bug; For e-mails that are not from people in my address book Rule Number 1: Mark them as read and clear message flag Rule Number 2: Move them to the Junk folder But, they are still marked as unread and the message flag is not cleared. They are, however, moved. The rules are in the right order (I triple checked them). If I run just the Rule Number 1, they are marked as read and the message flag is cleared. Any ideas? Thanks. ...

I have done something to make my email send button disappear
I can't send emails in my outlook 2003. I must have changed something to make the send button disappear. It used to have a drop down menu that would let me choose which email account to send mail from. Stephen Oatway <Stephen Oatway@discussions.microsoft.com> wrote: > I can't send emails in my outlook 2003. I must have changed something > to make the send button disappear. It used to have a drop down menu > that would let me choose which email account to send mail from. Sounds like Outlook doesn't see your accounts. Create a new mail profile with Control Panel...

Multi-level Category Axis Fails with >100 rows of data
I'm having the same problem as posted on a few sites, one of which is below. Is there any workaround, or plan to resolve this in the current version? Any help appreciated. http://www.tech-archive.net/Archive/Excel/microsoft.public.excel.charting/2009-02/msg00281.html In my scenario, I'm building a Gantt chart style Promotional Calendar organized by brand and then promotion name. The calendar can often have more than 100 data points in larger markets. I've built a Gantt chart off of a pivot table, with some VBA code to mimic functionality you'd get from sli...

Sum all rows above
I want to put into a cell the total of all numbers above that cell in that column. I want to do this without specifying specific rows. In this way, if a row were added above the total, it would be included in the total correctly. Robert Hi Robert, Try: =SUM(OFFSET(A1,,,ROW()-1,1)) HTH -- AP "Robert" <robert.crouser@gte.net> a �crit dans le message de news: 2Vk7g.1120$qo6.346@fe04.lga... >I want to put into a cell the total of all numbers above that cell in that >column. I want to do this without specifying specific rows. In this way, >if a row were ad...