Format Cell Based On Context Of Another

Jan 24, 2014

I want to format the cells in column B under the context of the cells in the corresponding row of column A. For example I want to color the cells in column B that the context of the corresponding row of column A is 1:

1
put

2
hot

4
dog

1
cat

1
yes

View 2 Replies


ADVERTISEMENT

Change Cell Format Based On Existing Currency Format

Jun 12, 2008

I would like to have a single button that changes a range of cells from the USD to EURO to perhaps CAD symbol. Can this operation be performed, such that if I start in dollars, and I click the button once, it shifts by range to EURO (not formulas...just symbol)...and then if I click the same button again, it goes to CAD, and then back to USD with a third click?

View 5 Replies View Related

Custom Context Menus For Any WB (using Add-in?)

Jan 12, 2010

Ok, so I have this neat little macro that on right-click brings up a custom context menu that displays a few different options - for example one such function is simply copying the selected cell content to a (specific) different sheet. It is a very useful feature in the context I am normally using it to merge information from different locations in a new structure (well, I think this is irrelevant, but just letting you know basically what I am doing).

Anyhow, to implement the custom context menu, I am using events in the ThisWorkbook object, namely Activate to add context menu entries and DeActivate to remove them if switching workbook.

Now, if I receive any workbook from someone else, I'd like to add this feature to get the custom context menu easily without having to copy the received workbook into a new workbook (it's a bit of a hassle, and ideally, if the received workbook has any other macros, this function could just be merged). I was thinking using an add-in to achieve this, but using the ThisWorkbook events in the add-in doesn't trigger the events (I guess because the hidden workbook in the add-in is never "Activated" or "DeActivated").

View 6 Replies View Related

Modify Context Menu For Chart?

Apr 19, 2013

Is it possible to modify the context menu for a chart? I want to create a menu option that runs a macro. I've found code that modifies the context menu for cells, but I haven't had any luck with charts.

View 2 Replies View Related

Vba - Context Sensitive Auto-complete

Jan 16, 2007

I had to crash Excel because it had got itself into a pickle - run out of memory or something anyway it was chasing itself up it's own backside.

I rebooted to restore memory and ever since then my Context sensitive auto complete on VB statements, subroutines and functions has vanished and I can't figure out how to get it back.

How to restore this as I have some seriously complicated routines that are MUCH slower to code if I don't have this option.

View 9 Replies View Related

Customize Right Click Pop-Up Shapes Context Menu

Sep 5, 2009

I'm using Excel 2003 and am unable to find the name of, and therefore customise, the right-click shortcut menu that pops up when you right-click on a line drawn from the 'Drawing" toolbar. Basically, I'm trying to do some technical analysis on a chart. I want to be able to right-click on the lines that I draw on the chart and replicate a parallel line.

View 3 Replies View Related

Excel 2010 :: Right Click Formatting Context Menu?

Jun 26, 2014

In Excel 2010 onwards (probably 2007 as well) there are two right click context menus that pop up on a cells.

The "Cell" command bar and also a smaller formatting bar.

What is this bar called and if you remove it how do you get it back when you have removed all the standard bars.

VB:
Application.Commandbars("Cell").Reset

The above doesn't seem to get back the formatting bar. I did managed to get it back but through luck rather than judgement. Just through looping through every commadbar and printing it to the immediate window.

I've added the name of the context menu to each commadbar but the formatting one does not get a name put at the bottom of it. I've looked on this site and it doesn't seem to say anything about this new menu.

View 1 Replies View Related

Adding Item To Filter Submenu On Context Menu

Oct 31, 2013

I have added items to my "Cell" context menu but was wondering if there is a way to move one of the items to the Filter submenu. Below is the code I am using to customize the context menuI want to move the "Filter Status" item to the filter submenu.

Sub AddToRightClick()

Dim ContextMenu As CommandBar
Dim ContextMenu2 As CommandBar

'This is for the normal view right click menu
Set ContextMenu = Application.CommandBars("cell")

[Code] .........

View 1 Replies View Related

Macro That Will Clear Contents Of Cell Based On Format Of Text In Adjacent Cell

Feb 18, 2009

Been racking brain, searching through the forum here, and my Excel 2003 Bible all day trying to figure out this problem to no avail. I would like to clear the contents of any cell in a given range if the cell immediately to the right of is formatted as bold.

View 2 Replies View Related

Conditionally Format Cell Based On Another Cell That Is Manually Colored?

Nov 24, 2013

let's say cell A1 is colored blue manually, how can i format cell b1 to be colored red? is it possible in conditional formatting?

View 7 Replies View Related

Change Result Cell Format Based On Input Cell Value

Mar 24, 2007

Macro Based on an input cell’s (e.g. B12) setting of “yes” or “no”, format several other cells (e.g. B20:G20, B23:F23, C26:J26) as follows: keep the existing yellow shading if input cell is “yes”, change to grey shading if input cell is “no”. I would like this macro to run automatically whenever the input cell value is changed.

View 2 Replies View Related

Format Row Based On First Cell Value

Sep 21, 2012

Y3:Y500 contains a range of years, 2014/2015 through to 2018/2019, for a total of 5 different values (So conditional formatting is out the window). I'd like code which highlights the rest of the row, Z:FN, in a certain colour depending on the value in Y3:Y500.

Also, where there is data in the cells between Z:FN I'd like that to highlight in yet another contrasting colour.

View 9 Replies View Related

Conditional Format Cell Based On Data From Another Cell

Nov 11, 2012

Can I conditions format a cell based on data from another cell?

View 8 Replies View Related

Conditionally Format A Cell In A Grid Based On Other Cell

Sep 12, 2006

way to conditionaly colour code all cells in a grid based on cells in other grid: i.e.

gridA
1 1 2
1 2 1
2 2 1

grid B
12 11 14
34 16 53
35 64 23

i want all cell in grid B to be red if the coresponding cell of GridA is 2.
so here "14" "16" "35" and "64" will be red!

View 9 Replies View Related

Numeric Format Based On Cell Value

Nov 17, 2009

I am stuck on this one. In the attachment I have values pulling into N3:N7 based on a drop down choice. Some of the numbers are numeric (no decimals), others are currency (no decimals), others are percentages (2 decimals), etc.

Is there a way to pull values I'm currently pulling into column N and have those values match the format of the source? I've tried converting it into TEXT but then I cannot place conditional formatting data bars on the values and have it work.

View 4 Replies View Related

Format Cell Based On 2 Cells?

Nov 6, 2013

If F2 is blank and E2 date is older than or equal to today, bold and color E2 RED.

View 2 Replies View Related

Return A Value Based On Cell Format

Aug 9, 2007

I don't even know if this is possible but can you use some formula within Excel to return a value to a cell based on the cell formatting of another cell? Here is specifically what I am trying to do:

Cell A2 has a value of 10. If the font color of that cell is black (or "automatic") I want a formula in cell A1 that will return a value of "+10". If the font color of cell A2 is red, I want the formula in cell A1 to return a value of "-10".

View 13 Replies View Related

Conditional Format Based On Cell Value - VBA

Jan 17, 2014

With the click of a toolbar button, I've got my vba inserting a new tab, renaming it, inserting headers and now working on the code to insert all the formulas needed in 12 columns to copy down as far as there is data.

The 4th column D is the hardest. Currently, I'm using manual "Conditional Formatting" to colorize the cells in Col D but need the VBA to place that "Conditional Formatting" code into the sheet so that when the sheet is created, it will hold the conditional formatting feature.

Specifically, the formulas I'm using in the Conditional format wizard are:

Formula: =$B2$D2 then turn the cell red Applies to: =$D$2:D

Copy down (in other words, the colorizing should work all the way down Column D as long as there is data in Col D.

View 2 Replies View Related

Conditional Format Row Based On Cell Value - VBA

Jul 23, 2014

How to write a VBA that will, if the value of a cell in column "U" is TRUE, that row's FONT (for the row the TRUE cell is in) will turn red.

View 4 Replies View Related

Copy Based On Cell Format

Nov 21, 2006

Is there a macro that can copy the information of any italicize item in Sheet1 to Sheet2 and automatically italicize the item in Sheet3 if the same item exist?

In my attached example:
In Sheet1, B3 to B8 contain information about a restaurant.
I want the macro to copy any restaurant (and its information) that is italicize to Sheet2. And in Sheet3, any restaurant that is in italic in Sheet2 will become italicize in Sheet3 if the name is match. Is there such a macro that can do these things?

View 5 Replies View Related

Conditional Format Cell Based On Value Of Another

Feb 19, 2007

I'm afraid as its a multiple conditional format and thus needs coding. At the moment I'm using the following

Private Sub Worksheet_Change(ByVal Target As Range)
' Conditional Formatting for more than 3 conditions
Dim rng As Range
' Target is a range::therefore,it can be more than one cell
' For example,,someone could delete the contents of a range,
' or someone could enter an array..
Set rng = Intersect(Target, Range("E27:E34,G27:G34,I27:I34,K27:K34,M27:M34,O27:O34,Q27:Q34,S27:S34,U27:U34,W27:W34,Y27:Y34,AA27:AA34"))

If rng Is Nothing Then
Exit Sub
Else.....................

This works very nicely, so that for example if I type the number 10 into cell M33, that cells fill colour becomes black (and the text turns white). Alas I would now also like to extend the fill and text colour to the cell one to the left too. To take this example I would type 10 into cell M33 and cells L33 and M33 would both be filled black and their text would be white. I'm not sure if this requires a change to the code, or if its possible to just have a formula or something which copies the fomatting of one cell into another

View 2 Replies View Related

Copy Format Of Cell Based On Value Of Another

Jan 8, 2008

I have been working several weeks on producing a macro that can quickly eliminate blank lines in an .xls file which is created by exporting a Crystal Report into Excel. The macro can do that, but the big problem is that it freezes up the users PC for 3-5 minutes. If you go into Task Manager you can observe that CPU usage is at 100%. The .xls files that I am trying to run this macro against are around 50,000 rows long with data in about 10 columns. About 2/3 of those rows are blank. This is an inherent problem when exporting Crystal Reports into Excel. I went to Business Objects website (Crystal Reports) and their solution is simply not an option. They suggest removing If-Then-Else logic. I simply cannot do that because that is the heart of how the report selects data for each row to print in the report.

Below is the code that I wrote with documentation to explain how it works. If you would like to try it I attached an .xls file. It is only 800 rows long so that I could stay under the forum's requirement of attaching nothing larger than a 48.8 kb file. The file has only one column, 800 rows long. You may want to copy and paste it a few times to make it longer. When running the macro it will open a dialog box so that you can go wherever you copied the file, and open it up.

Sub OpenXLSFile_DeleteBlankLines()
Dim fileToOpen As String
Dim lRows As Long
'open dialog box for user to access an Excel file for deleting blank lines
fileToOpen = Application. GetOpenFilename(FileFilter:="microsoft excel files (*.xls), *.xls", Title:="Select an .xls file to open for deleting blank lines...", MultiSelect:=False)
If fileToOpen <> "" Then
Application.Workbooks.Open fileToOpen
End If.......................

View 4 Replies View Related

Format Row Based On Single Cell Value In That Row

Jan 21, 2008

I have a macro that's supposed to see if cell 5 meets criteria and continue to all sheets in workbook, except " Total".

When I activate the macro on a specific page, it runs smoothly on that page. But when it's finished, it doesn't continue to the next sheet in the workbook.

Sub ColourBG()
Dim ws As Worksheet
Dim line As Integer
endline = Range("A1000").End(xlUp).Row

For Each ws In Worksheets
If ws.Name <> "Total" Then
With ws
For line = 3 To endline
Application. ScreenUpdating = False
If (Cells(line, 5).Value = "0206") Then _
Cells(line, 1).EntireRow.Font.ColorIndex = 5 '*(Blue)
Next line
Application.ScreenUpdating = True
End With
End If
Next ws
End Sub

View 9 Replies View Related

Format Cell Number Based On Value

Feb 19, 2008

I need to evaluate some offers and for the management I need to follow some strict rules like:

1. € 1.000.000 instead of €1000000. But that's not the problem
2. to use maximum 3 digits... Example: 10,2 or 6.12

I tried with an if function and then round, but I think that's not right way. If number < 10, then round with 2 decimal places else, round with 1 decimal place

View 3 Replies View Related

Format Cell Based On Value Within Multiple Columns?

Feb 16, 2013

I want to color code cells within my workbook based on certain criteria. The workbook spans 17 columns and about 400 rows. Each cell contains a team name or is blank. I can't seem to find a good method to find a cell and change the cell color and then move on to the next cell. I have attached a workbook with actual data that I am working with. I am attempting to use case statements but perhaps they are limited to only one column or I am just missing something. I have spent many days searching the forum and have found no solution. Any help would be greatly appreciated.

All I am trying to do is make it easier for Team Managers to find their field and time slots based on the cell colors. Below is the code that I have started:

VB:
Sub TeamColorFilter()
Dim myCell As Range
'Set MyPlage = Range("A3:Q390")

[Code]....

View 3 Replies View Related

Conditional Format - Highlight Cell Based On The Value Of Another?

Jul 24, 2013

Looking to use conditional formatting to highlight a cell based on the value of another.

I.E. Highlight Cell B2 if Cell A3 is 4 or greater.

View 2 Replies View Related

Format Cell Based On Column Of Dates?

Mar 23, 2013

I'm trying to format a singe cell based on if a row of dates is aged more than 3 days.

Column C is for Load Date. Rows C2:C100 may or maynot have a date in them. Cell C101 is a count (=counta(C2:C100) of how many cells above contain data.

I'm wanting to format C101 to turn red if ANY of the dates in C2:C100 are older than 3 days from the current day.

View 4 Replies View Related

Conditioning Format Based On Adjacent Cell?

Aug 19, 2013

How would I make cells in column A to C be highlighted yellow, of which is 0.02 in value be higher than in compared with the adjacent cell in column D ? It is easy to use condition format function to setup the conditions in 1 row. The problem is there are thousands rows in the sheet(of which represents an individual file by each row), and need to have the conditioning format applied on each row independently. How can it be done w/o the need to have format painter applied line by line, of which is a very time consuming.

View 6 Replies View Related

Conditionally Format Currency Based On Another Cell?

Feb 28, 2014

What I am trying to do is create a multi-currency expense report. For example, cell B1 has a drop down list of three different currencies. Based on the currency selected in B1, I want C1 to reflect that number formatting using the proper currency.

Im sure there is an easy way to do this but I have tried using an if statement in the conditional formatting section but it does not work.

how to total the different currencies using the ifsum formula, so this is more of a presentation task, but still it has annoyed me to no end.

In the example workbook, the currency value selected in row E should effect the currency formatting in Rows F-L

View 1 Replies View Related

Work Out Time In And Out Based On Cell Format

Jan 14, 2009

I have a sheet that I want to calculate what time a shift starts based on a cell shading.

Basically Cells C6 and D6 need to be auto calculated based on the cells that are shaded from G6:L6

Look at the Attachment and you will see what I mean.

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved