Anyway I want the first line (the one that shows the dept) BOLDED. I thought I could do dhtis with conditional formatting, applying it the sheett all at once by saying if the value in row A > 0 then bold the line..........but I am not doing right...
PHP Dim B as Rangeset B = activesheet.range("A1:C3")Dim a(3) As Doublea(1)= 1.2a(2)= 3.6a(3)= .27
B is a 3x3 Matrix of values in your sheet
How would I perform matrix multiplication of B * array "a"?
My main problem is with how to reference then entire array "a" and how the data is being stored (is the array a 3x1 or a 1x3). And if there is a VBA MMULT command or if building my own or using the worksheet one are my only options.
I have been looking into refining the way a couple of big macros that I wrote several years ago work and have decided that instead of loads of inputboxes that a Userform might be the way to go. I have never used them, don't know how to link code to the objects to make them work and can on make a guess as to the right way to start going about this. Need sort of Userforms for Dummies site where I could pick up the basics? I am going looking toward having a box come up where the user can enter about 8 pieces of data and based on that data the macro will then go off and open specific files anc change specific data.
A little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.
I have an excel sheet that have a large set of datas. In these datas in one column most of the datas are indicated as green but some of them are red. I can pick the red ones by using filter option but I also wanted to show the red cell values in one cell. For example, Cell1;Cell2;Cell3 like this (the ; should be between them). I try to use macros but that is not the program that I have full knowledge about it. S
I have 2 spreadsheets: in a Sheet1 I keep some values and in a Sheet2 5 names of users with special rights (range A1:A5). I want to have sure that anybody will change/delete that names without password. Only the user "Administrator" can do that(I check the name of the user using "Application.UserName"). Additional question: is it possible to still protect those cells if somebody turn off macros during opening file?
i have been working on editting a game that i got off the internet and i was having trouble editing the buttons on the worksheet. they dont seem to be linked to a macro and i cant edit them or even delete them it seems like they are forms but attached to the worksheet.
Is there "code" for different formatting in a spreadsheet so you can use an IF statement to do something like:
etc.?? But replace "blue background" and "red text" with some sort of number code? I want to compile a list of the items that are formatted with certain background colors and/or text colors and then organize only those items into a chart.
Any VBA that I can put into a macro that will convert conditional formatting into fixed formatting..? So when the cell contents/formulas are deleted the formatting remains. Assume that the range I want to convert is A1:D200...
I am trying to create a user form to edit some named ranges. The VBA user form designer is basically the same as Borland Delphi, so building the form itself was easy. What I can't figure out is how to populate the user form with the data from the spreadsheet.
I have a number of text boxes on the form with names like txtTier1Slots, txtTier1Ceiling, txtTier1Floor, etc up to 4.
I have a defined name that corresponds with each item, Tier1Slots, Tier1Ceiling, Tier1Floor, and so on. I'm using defined names because they're referenced in Conditional Formatting on one of the worksheets in my project. They don't exist as actual cells, just name references.
I want to get the form populated with those values. I've tried about twenty variations of the following code, using ThisWorkbook, Workbooks, Cells, Range, Name, and just about every cell reference method I can think of. I've so many different error messages, my head is spinning.
User Form Basics - Populating Text Boxes. A few tweaks later and its reading the data just fine. Some of the specific form objects and range names have changed, but it's the same logic.
Anyway, this isn't just for display. I need to export the data back to the spreadsheet. So, I plugged this line into the event handler for the "Save & Exit" button on the sample form in the other thread:
I need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.
I have a hard time when it comes to "If then" statements and conditional formatting. I have some data that I am collecting and entering into a spreadsheet for a couple different companies. Once I receive paperwork from those companies for the data collected I mark the data with a blue colored text. I would like to have a second sheet that generates what companies have not sent me their paperwork. "If the data is black text, then generate <name of company> on second sheet." The list is small right now, but as the project continues it will grow very large and it will become difficult to track. I will attach a copy of the spreadsheet for you to see the situation. I would like to learn how to do these types of formulas, is there a tutorial geared towards these types of situations?
Must admit the R1C1 still confuses me, but somehow I have this working. Currently the RC4 displays in conditional format as $D8 and the RC4 as $C8, but I'm confused at how it knows to start at R8? Is there any way to make the formulas read ="=$D8=""No""" instead?
I have two spreadsheet "book1" with data in it, "book2" that takes all the information from "book1" the cells are linked in "book2" from "book1" so when someone updates "book1" the information is viewable in "book2"
In "book2" where I am getting stuck is as follows,
Column A contains data "break time" that is formatted in HH:MM:SS column B contains a "total duration" in HH:MM:SS. what I need to do is if column A data is = to or more than 4% of column B then I want to turn the cell in column A "red" if it is less then turn it "green". I have tried conditional formatting and it appears to work the once only, I dont no if this is because its 2003 and not 2007??
What I need is to repeat the conditional format over and over again forever is there any code for this I can use?
I have used conditional formatting various ways to format an individual cell. I was wondering does anyone know if it is possible to conditionally format a row? Eaxmple: If D7=Manager, then format A7 through Z7. If so, how would I go about setting that up?
I'm wanting to create a formula in column B that will return a value of 10 if column A is any value between 70% -100%, will return a value of 5 if column A is any value between 50% - 69%, and will return a value of 1 if column A is any value between 1% - 49%. Can you help me?
Sub Macro1() With Range("A1") .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:="=A1=B1" .FormatConditions(1).Interior.ColorIndex = 3 .FormatConditions.Add Type:=xlExpression, Formula1:="=A1B1" .FormatConditions(2).Interior.ColorIndex = 50 .AutoFill Destination:=Range("A1:A29"), Type:=xlFillDefault End With End Sub
If my active cell is NOT A1, the formula entered into conditional formatting is off. The range references get all messed up. For example, if my active cell is Say D5
Condition 1 is =IT65533=IU65533 Condition 2 is =IT65533IU65533
But if I run the code with A1 as my active cell, it gets entered correctly as Condition 1 =A1=B1 Condition 2 =A1B1
I would like to add conditional formatting for the whole row based on whether the value of cells in column A is "CON".
For Each c In r If c.Value = "CON" Then c.EntireRow.FormatConditions.Delete c.EntireRow.FormatConditions.Add Type:=xlExpression, Formula1:=c.Address = "OK" c.EntireRow.FormatConditions(1).Interior.ColorIndex = 35 end if next c the "CON" is a formula and can later take the value "OK".
The conditional formatting should highlight the whole row as green when that happens.
I am not sure if this requires conditional formatting/vba function
If I enter something in cell A1 and leave blank in cell c1, in cell c1 it should print the message "Required Entry, Please Fill up". and this condition should be applied to entire column, where ever in column A there is some values and column c is empty this condition should apply.
I don’t know if it is possible in Excel 2000 but I have a simple IF formula in a cell that gives "" on certain conditions.The problem I have is that in another cell I apply conditional formatting – green >100%, red<100% e.g.
Is it possible to have a third conditional format that gives a blank if the cell is ""?
At the moment it just turns out green as it doesn’t seem to recognise the cell as actually been blank as it has a formula within it.I have tried ISBLANK etc.
Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer Dim cr As Date cr = Today() If Not Intersect(Target, Range("C5:M44")) Is Nothing Then Select Case Target Case "X" icolor = 10 Case "N/A" icolor = 2 Case cr.Value >= Today() icolor = 6 Case cr.Value <= Today() icolor = 3 Case "DATE?" icolor = 8 Case Else icolor = 2 End Select Target.Interior.ColorIndex = icolor End If
I've written a macro that copies a range of cells (size varies) from Sheet1 to Sheet2, deletes the conditional formatting already there, and applies fresh conditional formatting to Sheet2 on the new range. But, the expression in the FormatConditions is way different than what I specified in the code. Here's my Conditional Formatting
Set mt1 = tData.Columns(1). Find(What:="", After:=tData.Range("A1"), LookIn:=xlValues) tData.Range("A2:A" & mt1.Row - 1).FormatConditions.Delete With tData.Range("A2:A3" & mt1.Row - 1).FormatConditions _ .Add(xlExpression, , "=A2=A1") .Font.ColorIndex = 2 End With
When I look in cell A2, I see this for the expression: "=A65301=A65300". Any idea why it isn't starting with "=A2=A1" as I specified in the code?
I am trying to create a macro (which will go in an add-in, using Excel 2007) which will apply a custom format to any selected cells which have their formulae hidden (Format Cells, Protection, Hidden). A similar macro works fine for locked cells.
Here is the UDF I wrote, which returns True/False based on the Hidden status of a cell:
VB: Public Function Hidden(Check_Cell As Range) Hidden = Check_Cell.FormulaHidden = True End Function
just place all three pieces of code into a module, and change the first line of the two macros to a standard "Sub Macro1()" type format.