Conditional Formatting With Sharepoint Data Connection

May 30, 2007

I have an Excel workbook that has a data connection to a Sharepoint List. I have 4 columns showing the quaterly status of a project. I want to apply conditional formatting based on the value in the column. I have 5 possible choices so I have to use VBA code to accomplish this because of the limit of 3 option in Excel. I am using this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Intersect(Target, Range("X:AA"))
If rng Is Nothing Then
Exit Sub
Else
Dim cl As Range
For Each cl In rng
Select Case cl.Text
Case "1"
cl.Interior.ColorIndex = 4
Case "2"
cl.Interior.ColorIndex = 6
Case "3"
cl.Interior.ColorIndex = 3
Case "4"...............................

View 3 Replies


ADVERTISEMENT

Number Formatting Not Converting Into Connection Worksheet?

Jun 3, 2014

I have an excel document saved in a SharePoint document library. I then have another excel document where I created a connection back to the document being saved on SP (went to data connections->add->browse for more->enter the SP URL & found the document) and then opened that connection up as its own worksheet (existing connections->chose my connection). In the original worksheet (saved on SP), there are about 20 columns that are formatted as numbers. However, when I open the connection up in the new document, half of these columns are still number formatted, but the rest are being displayed as text EVEN THOUGH they are technically formatted as numbers (I right click->format cells & they are formatted as numbers). I can click into a cell and press enter and it will "come up to speed" and enter into number formatting, but as soon as I refresh my connection back to the original document, they go back to their text formatting despite the fact that the connection they are drawing from has them saved as numbers! Very frustrating. Its not a SP issue because I recreated the scenario by saving the original document to my computer and it did the same thing. I even went through one column in the original document and made sure there were no spaces saved to trigger the column to go to text- nothing.

View 2 Replies View Related

ADO ADODB.Connection: Preserve Cell Formatting

Oct 4, 2006

With ADO (ADODB.Connection), is there a way to preserve the cell formatting on the worksheet that the recordset data is copied to? Right now, if I format a Cell's font and font-size, then run the Macro to refresh the data, the formatting is gone and it's back to default formatting.

View 2 Replies View Related

Publish Data To Sharepoint Calendar

Nov 16, 2007

I use an excel report that connects to an external sql datasource and using macros imports and arranges the data showing dates, job number, description and author of various jobs coming up at work.

I want to publish this spreadsheet's contents onto a calendar in excel or (preferably) onto a sharepoint calendar.

Does anyone know how to:

1) Publish the spreadsheets contents into a calendar in excel?

2) Publish the spreadsheets contents onto a calendar in sharepoint?

View 3 Replies View Related

Macro To Copy Data From One To Another Sharepoint File

Apr 16, 2014

I created a macro on an excel file on my local PC that opens another excel file on my local PC and copies some data from file 2 and pastes that data in file 1. In file 1 have a Path and File Name that the Macro uses to get Open File 2 from. This works Great on my local PC!! However, I need to move this to a SharePoint. When I did I tried to change the Path and File name to the ones from the URL generated by SharePoint Send Link. No Luck, then tried to change the 20% replace for spaces. Still no luck. Do I need to use some other method of opening a file that is on SharePoint vs Local PC?

Currently have I have:

Sheets("Labels").Select
Path = Range("b2").Value
BenFile = Range("b3").Value
InterOpFile = Range("b4").Value
NMOCFile = Range("b5").Value
SECFile = Range("b6").Value
VistaAFile = Range("b7").Value

and Later in the Code:

Workbooks.Open Filename:=Path & BenFile

View 1 Replies View Related

Conditional Formatting Groups Of Data

May 28, 2014

I have a spreadsheet with data in a table with order numbers in column A. Although each order is given a unique number, the data populates with multiple rows, one for each line of the order, all with the same order number depending on how many lines where on the order (which is generated via other software and cannot be altered). Therefore the data, when sorted by order number, appears in groups:

[URL] .....

Is it possible to conditional format each line in groups of the same order number to make it easier to differentiate between each individual order which will still work when the data is sorted or filtered?

View 2 Replies View Related

Conditional Formatting - No Data No Colour

Nov 4, 2008

On this particular formula but my colleagues have informed me that they require cells with no dates in to have no colour fill. Firstly, I have searched and tried myself with no success on stopping the below formulas from filling empty cells with red, they are as below;

Code below for 11 months after entered date:

View 2 Replies View Related

Conditional Formatting - Colour A Whole Row Of Data

Jun 8, 2009

I am trying to colour a whole row of data (in columns A through to AA) based on the numeric entry in column AB. The numeric entry is either 1, 2 or 3. I reckoned I needed to use the IF function, but as writing formula is not my strong point I am struggling to enter the correct data! The first row of data I wish to use is 3. I have tried the following but to no avail: =IF($AB3,1). =IF($AB3=1). =IF($AB3="1").

View 5 Replies View Related

Data Bars Conditional Formatting

Mar 5, 2012

I am using the simple conditional format "Data Bar" to make a progress bar, going from 0-1 (0-100%), works fine .5 is halfway...ect. but what i would like it to do is to change to a different colour only when it reaches the 100% or 1 in this case so you can see quickly that this progress is complete.

View 7 Replies View Related

Data Validation And Conditional Formatting Using VBA

Jan 14, 2010

I am using Data Validation for my drop down list, using “List” and “source” =$A$132:$A$159 along with Conditional Formatting that changes different fill colors, depending on selection. Is there a way to use VBA with a command button that when selected would give the user a dialog box so that they could enter additional comment that would be added to existing list and would also be able to select a fill color for that new selection.

Below I have a recorded Marco to give an idea of what I would like to achieve, but of course using a input box of some sort to make selections of text and color

Also I'm using Excel 2007

View 14 Replies View Related

Conditional Formatting From Data On Different Sheet

Jan 24, 2014

On one of my excel spreadsheets I have someone's name and a drop down list of "subscribe" or "unsubscribed" in the next column over:

Name

Company
Subscribed?

Name1
amazing company
Unsubscribed

[Code] .......

I have their names in column A on another sheet and a load of contact details and details on subscription price etc. I want to be able to conditional format these rows of details according to if they are subscribed or not. I'm struggling to come up with a formula that works and the conditional formatting menu isn't working much.

View 5 Replies View Related

Data Validation & Conditional Formatting?

May 21, 2009

I have conditional formatting for a range of cells eg. =COUNTA(AC3,AD3,AE3,AH3)
It is currently set so that cell A3 changes to Green if AC3 is populated, Red if AD is populated, Orange if AE3 populated & white if AH is populated.

AE3 has data validation (Yes or No).

I need AE3 to change to White if no is selected and Orange if Yes is selected.

View 9 Replies View Related

VBA Conditional Formatting - Refresh When Data Changes

Jul 13, 2006

I searched, but couldnt find a thread which covers a VBA code which covers writing a conditional format into cells and changes each time contents of the cell are refreshed. The conditions are:

If activecell.value < 0 Then
.interior.colorindex = 3

If activecell.value >=0 Then
.interior.colorindex = 4

and i want this to happen for a big range of cells (range(a1:k1500) So basically, once the macro is run(which has the code to insert the conditinal formating for all the cells), if the user makes any change to the cell(within the range), the color of the cell should change automatically(like Conditinoal formating works by default). Prior to this, i was doing my cell coloring using the 'for loop' approach, but that of course does not reflect once the macro has ended.

View 2 Replies View Related

Conditional Formatting Based On Cell Data?

Jan 29, 2014

I would like to be able to change the color of fill in a cell if there are 3 occurrences in cells. Attached is a sample.

In the attachment, if the cells A2, D2, G2 all have a number "1" in them I would like cell K2 to have a color fill for the cell.

The same would be for the other cells, if B2, E2, H2 all have the number "1", K2 should have a fill in the cell as well, same as if C2, F2, I2 have a number "1", fill K2.

I only want the fill color to be in cell K2 if all 3 of the cells, ex: A2, D2, G2 have a "1" in them. Also, some of the other cells may have a number "1" in them but I would not want them counted unless it is 3 of the consecutive cells.

View 7 Replies View Related

Conditional Formatting On Cells With Data Validation

Jul 24, 2009

I have column of cells, both of with have drop down menus to select information from. I have an adjacent cell to display an error based on conditions. Is there a way to have conditional formatting to apply to the cells with Data Validation to change the color, based on the error. I have tried using the Conditional Formatting options but it still does not change the color of them. I believe i have to use the formula option, but not exactly sure what to put, i know a if statement could work but unsure on how, there are only two conditions that i would need for it to apply, if the adjacent cell has an error, and when it does not. Is this possible?

View 9 Replies View Related

Conditional Formatting Using Formula For Data In Cells

Oct 13, 2009

I am trying to conditionally format some data in cells A3:A6 (shade the cells) depending on whether a number in B1 is 1 (green), 2 or 3 (blue), or 4 (red).

View 3 Replies View Related

Data Validation Lists And Conditional Formatting

May 19, 2008

On sheet 1 I enter data such as:
Visit Date | Customer Name | Customer Number | Job Number | Product

On Sheet 2 i have a list of our 130 products which would be similar to '1130Tots Standard 24" ' I have made these as a list so we can choose from the list using data validation.

Now, out of the 130 products we have about 4 main different Catagories - Tots Direct, Tots RTS, Direct, RTS. - I could put what category they are in in a column next to the list on Sheet 2.

What i would like to happen is if e.g. a product is chosen from the drop down list, it could look in the cell in the column next to it and see what Catagory it is in and format the entire row on Sheet 1.

E.g. Direct changes colour to grey, Tots RTS to Pink, Tots Direct to Grey/Pink stripes, RTS stays standard no colour.


A bit of a development on a question i asked last week to cut out another stage. Currently people have to enter the product, then choose in the next column whether is is Direct, RTS, Tots Direct, Tots RTS then conditional formating will colour the rows. - Want to know if it is possible to do this all in 1 go by selecting the product from the list.

View 10 Replies View Related

Excel 2010 :: Access Data Connection Query - Missing Data All Of Sudden

Oct 19, 2012

I created a slick little excel sheet with the data coming in automatically via Access query. It has been working fine for months. Now all of the sudden there are a bunch of cells with missing data. The weird part is it's not as if whole columns are missing data, more like 90% missing. When I go to Access and run the query all cells are populated as the should be. There have been no changes to the query at all during this time.

btw...I am running Office 2010

View 2 Replies View Related

Data Connection From Web / Web Query Returns Code Instead Of Selected Data Table

May 31, 2014

A web query I am trying to use to bring an online data table into my worksheet is broken. Now, instead of returning the data table nicely into my worksheet, it imports the code of the webpage, instead, and turns my worksheet into a mess.

The query used to work but there was recently an "upgrade" to the program that populates the web table and the query no longer works and just returns the code for the page, instead. I can see the html code for the table in all of the code it returns but I really need the table to import cleanly into excel.

I can't talk to the people who changed the web program.

View 3 Replies View Related

Conditional Formatting Highlighting Data That Matches On 2 Different Worksheets?

Jul 17, 2012

I have 2 worksheets. One has locations with numbers, example:

275 Location 1
276 Location 2

I have all my locations on the second sheet, but in 2 different columns, listed with numbers only, example:
271 275
272 300

I have 2 scenarios I need help with.

1st:
If any of the numbers on sheet 1 match the numbers in column 1 on sheet 2, highlight the number on sheet 1 in green If any of the numbers on sheet 1 match the numbers in column 2 on sheet 2, highlight the number on sheet 1 in red

2nd:
If the number on sheet 2 matches any number on sheet 1, highlight green

I want these to apply to all the cells that have numbers (it could apply to all cells I guess as it should ignore it if it doesn't match, I would assume)

View 7 Replies View Related

Conditional Border Formatting Formula (after Repeat 5 Data)

Apr 9, 2014

how to make conditional formatting for border line (top & bottom) after repeat 5 data/text ....

it's possible with CF formula and do not vba?

see my excel file attached..

View 4 Replies View Related

Conditional Formatting If Cell Contains Data Highlight Red If Blank Do Nothing

Nov 4, 2012

I have a spreadsheet where I have a column of dates and I want conditional formatting to highlight the cell red if the date is less than or equal to today but if the cell is blank to do nothing.

At the moment I have the following formatting applied using the "format only cells that contain" option I have cell value less than or equal to =TODAY() except that obviously highlights every cell red that doesn't contain a date. Is there another conditional format I can apply in addition to this that will not highlight the blank cells ?

View 4 Replies View Related

Highlight Cells If Data Differs By Using Conditional Formatting

Feb 6, 2014

Refer to attached sheet. I have 2 sets of data to compare.

B4:H30 and K4:Q30

Compare B4 with K4,C4 with L4 and so on.

If any data is not equal then highlight.

Compare Data.xlsx‎

View 3 Replies View Related

Sorting Data On Conditional Formatting / Font Color?

Feb 7, 2014

I work with some stock index data and I would like to get rid of some dots and empty spaces etc. I used conditional formatting and changed the font color of all the values I need. When I try to sort them based on font color (in order to delete the empty spaces/dots) Excel does not do it. "Go to...conditional formatting" also doesn't work.

View 5 Replies View Related

Data Validation And Conditional Formatting Unable To Save?

Jun 4, 2013

I have a pretty simple spreadsheet (because I'm not an expert on excel) to track temp staffing requirements each week over a year. It only has about 150 rows and a bunch of columns but when I try to make it bigger (add more rows so I can put about 70 staffing positions instead of 19) it will not save (cannot save all of the data and formatting). I have read on the microsoft site that there are limits to data formatting, but it describes over 2000 rows?

View 3 Replies View Related

Conditional Formatting If Cell Does Not Meet Data Validation

Dec 12, 2012

I have a table that is a list for validating values in a column .what I want to do is instead of getting an error message anf stopping if the entered value is not valid the cells become red.

View 6 Replies View Related

Conditional Formatting - Highlight Numbers In Data Group

Mar 25, 2013

How do I do "conditional formatting" to high light the numbers which bigger than average + 2 * stdev in a data group?

View 4 Replies View Related

Conditional Formatting :: A1 Is Equal To Data Highlight Cells On A2

Dec 15, 2009

how to create a conditional formatting formula that looks like this..

"If A1 is equal to DATA highlights greater than zero on cells A2 to A1000.

View 9 Replies View Related

Running Code On SharePoint Linked Worksheet To Update Existing And Future Data

Aug 8, 2012

I inherited a spreadsheet to manage that is linked to a SharePoint table.

It is trying to populate a date that a certain "Tier" is selected (1, 2, 3, or 4).

It works great if I manually type in the tiers, but does not run on existing data (about 400 records) or lines that are updated and new via the SharePoint list.

How can I have this run on all of the existing lines and anything added or changed in the future from the list?

VB:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("AD2:AD10000")) Is Nothing Then
Application.EnableEvents = False

[Code] ....

View 9 Replies View Related

How To Remove Data Connection

Aug 4, 2014

I was messing around with data connections and importing stuff from a network in real time. Cool stuff. But everytime I open ANY excel worksheet on this computer, it tells me "file cannot be found" and refers to one of the first files I used for this. this happens no matter what workbook I am opening.

When I click data --> connections its empty.

View 2 Replies View Related







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