I have a column of names, some of which are manually formatted with the strikethrough font.
I need to know how to count the cells that contain text formatted in this way. This total will then be used within a longer formula to produce a net result.
I have found tips on counting coloured cells but none that seem to apply to this form of font format.
I need a count that will ignore font that is strikethrough. Is that possible?
View 8 Replies View RelatedI'm trying to sum up a column of cells which have both text with and without strikethrough formatting. I've looked around and got some VB codes but it doesn't work for me.
I still get the annoying #NAME? error. It did work initially but after moving the file to another computer it just became #NAME? again.
I'm looking to set up a spreadsheet whereby individuals answer questions and have to format their answer using a particular font, colour, font size and so on. The idea is that I can then compare their answer sheet to a pre completed one using an =IF function and get a total score. The only problem is =if and =exact only lookup cell text/numbers and don't look at how the text is formatted within them.
View 9 Replies View RelatedI need to count cells in a column that display a red font as a result of conditional formatting triggered by the MAX function in adjacent rows.
I've tried numerous VBA codes suggested by your site and others but without success.
They work if I manually change the font colour but not when they're changed conditionally.
I'm looking to create a procedure to look at a specific range of cells, then remove the cell contents for those that contain strikethrough formatting. How do I go about that? Can this be done without looping?
View 5 Replies View RelatedWhat I am looking to do is;
If cell A2 = "Closed"
then I want cells B2 and C2 to strike through its own text.
I.E.
A2= anything other than "Closed"
then B2 and C2 = Blah blah blah
but if
A2 = Closed
then B2 and C2 = Blah blah blah
I have text of size 14 and 18 mixed in cells in a column. Cells are font size 18 or mixed with both 14 and 18 size text. I need to sort out the text with one column of size 14 and another of 18 only. I am thinking of copying and pasting the column twice and run a macro in first column to remove the text of size 14, and another macro to delete text of size 18 in second column. I need the leftover text to be in same rows.
I tried everywhere and couldn't find a macro for mixed text cells. I am using Microsoft Excel 2010.
I have a long list of cells each one containing a list of attributes similar to the following:
MBLB
MB9A
MCDA
MCDB
MCDC
MCDG
MCD7
MCD8
MCD9
MCE+
MCE0
MCE9
MCVO
MCVS
MEW5
ME2V
MLRP
Bear in mind that the above is in 1 cell and in-between each attribute is an - Alt Enter
Now I want to know how many cells a certain attribute is contained in e.g. "MCE+". I can do this on a one by one basis using the "Contains" Filter. However I would like to have the FULL list of my attributes and know how many times each one is mentioned.
I have table in excel 2003, where in one column I have different values in cells, such as A, B, C, D,E, F, etc., etc ( as text values as an example) plus some blank cells, how to calculate number of cells with values different from/not equal to B,C without blank cells?
View 9 Replies View RelatedSay the Range is (D3:M3) there is data in D3,F3,J3,K3,L3, and M3. I want to count consecutive cells with data from M3 backwards to D3. In this scenario the sum would be 4.
View 7 Replies View Related It is supposed to take the rows with striketrhoughs and transfer them to the other sheet.
Private Sub Worksheet_Activate()
If Cells.Font.Strikethrough = True Then
Rows.Select
Selection.Cut
Sheets("Completed Deployments").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End If
End Sub
I have this code below, which someone from this forum kindly kindly gave to me for my Worksheet code. What it does is check the value in a dropdown list in column I and if it says "Cancelled" then strikethrough columns A-K of that row.
What I wanted to know is this, in cell N2 has the formula to show todays date. In column D9 onwards is an end date. How can I amend the code below so that it also checks to see if the date in Column D9 onwards is greater than N2 and if so strikethrough columns A-K in that row?
Would it be in the form of an OR statement in the code below?
Is it possible to have a worksheet code that would underline a row if a certain value is selected in a dropdown list?
I have a worksheet with data populated across colums A to K, in Column I there is a dropdown list. What I wanted was to be able to understrike the Columns A to K of a particular row is the value in the dropdown list in Column I ="Cancelled". I cannot use Conditional Formatting as I have already used the maximum 3 formats for other features.
I have a workbook with multiple sheets. I want to use VBA macro to delete the row where the first column is strikethrough. I try to the follow VBA code but it doesn't work.
Sub Macro1()
Dim cell As Range
Dim delRange As Range
[Code]....
The following code does everything I need it to except for some reason it is not sending the font colour through first or the font colour is getting overwritten. I have formula in other worksheets that are counting the red and the blue to determine which rider in a team it is. For some reason the time sent to "B Grade" worksheet is ending up in black font. Can anyone explain to me why this is occuring.
I am using worksheet function.text because I am working with elapsed times that could be over 24hrs. I was wondering if the default text colour for the worksheet.text() function is black and if it could be changed.
I'm trying to have the font color of a checkbox text change according to what's in a certain cell. For example, if cell "A1" = "Boom" then the font should be red. This is what I've written:
View 3 Replies View RelatedI have text boxes in excel that hold text (obviously) over a period of several days. It gets updated daily, so I want to change the font color for the new text that is added daily rather than all the text. Is there a way to do this?
Also, is there a way to get the verticl scorll bar to always show on the right hand side of the text box? The way it is now, I have to click once or twice in the text box before it shows up. I would like the person reading it to automatically know that there is text not presently in view so they know to scroll down for more information.
I have executed this macro from this spreadsheet many times but today, after I execute the macro, the box increased in size. I repositioned it on the page, resized it and re-executed. Now it is decreasing the font size within the button each time I execute. It executed just fine yesterday. I assume it's some setting in excel because I've tried another spreadsheet and am seeing the same result.
View 1 Replies View RelatedI'm trying to select the font size of a text in a created shape. My actual code is not working.
Code: ActiveSheet.Shapes("Rectangle 46").TextFrame.Font.Size = 8
What is the proper way to address it?
When a number is entered in cell S3, it triggers formulas throughout the worksheet to populate the it with information from another sheet. If the length of the text in B6 is greater than 80, the font size for B6 only should be 8; if the length of the text in B6 is less than or equal to 80, the font size for B6 should be 10. Regardless of the length of the text in B6, the font size for the rest of the sheet should not be changed.
I tried the following
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address "$B$6" And Target.Count > 1 Then Exit Sub
Dim cnt As Long
cnt = Len(Cells(6, 2).Value)
If cnt > 80 Then Cells(6, 2).Font.Size = 8
ElseIf cnt < 80 Then
Cells(6, 2).Font.Size = 10
End If
End Sub
I was just wondering how I can type in a different colour. I.e. I have a document and I want to type in random cells but I always want to be typing in red, or blue etc. Hoe can I change the default text color?
View 8 Replies View RelatedI have a button (shape) whose text value is linked to cell A1. (In other words, if text in cell A1 changes, the text on the button changes). A concatenation formula exists in cell A1 to "join" together a combination of text and cell values. The formula in A1 looks something like this:
="You have "&B10&" records that contain errors."
I'd like to set the font for the "&B10" portion to be a different color than the rest of the text string so that it stands out from the rest of the text. Obviously this can be done when the text is manually entered into a text box, but when using a formula to populate the text in the text box, it's all or nothing. I'm hoping to find a way to embed a separate font color within the formula string in A1 for just the portion I want to change? For example, all text would be regular/black, but the numeric value generated from cell B10 would be yellow.
Is there any way to change font style, size and colour of text in a combobox?
View 1 Replies View RelatedHave a HUGE excel 2007 sheet of over 1500 rows that looks like this:
A1IDS09C1_PEND_COB2345678910IDS15C1_HIST_GATIL111213141516Sheet3
I need to get it to look like this:
A1IDS09C1_PEND_COB2COPY YES SHRLEVEL CHANGE SORTKEYS SORTDEVT SYSDA3BMCSTATS YES UPDATEDB2STATS YES UNLDDN
RU0014CONDEXEC YES UNLOAD RELOAD REDEFINE NO DELETEFILES YES SORTNUM 125DDTYPE WORK ACTIVE YES
DDTYPE SORTWORK ACTIVE YES6DDTYPE LOCPFCPY ACTIVE YES7DSNPAT
[Code] .......
In other words, I need to copy the text in blue so that it is above the text in black and the text in red so that it is below. Then I need the macro to repeat this procedure for each cell that it finds containing text in black. I have already created and run a macro to insert blank lines between each black text entry.
if a macro or private macro is necessary, I would need basic instruction how to use those as well (I mentioned private macro as I understand they automatically run whenever worksheet info is changed, which I would like in this example.)
I'm simply trying to change fonts in cells based on the inputs of other cells. Obviously, using conditional formatting doesn't allow for this.
For my example, assume the word "MrExcel" is in cells D2, F2, H2, J2, L2, N2, P2 and R2. If I type different font names (Times New Roman, Arial, Bookman, etc.) in the cells to the immediate right of each word "MrExcel," -- cells E2, G2, I2, K2, M2, O2, Q2 and S2 -- I would like the "MrExcel" word in each corresponding cell changed to the corresponding font to the right. I would like the ability to do this for up to 15 different fonts. There will be many rows to this worksheet I need to use this process for.
Is this possible? I'm in a bit of a jam and have exhausted my knowledge of formulas in that they won't work in this case ... I'm assuming I need to delve into the unknown land (to me) of macros, private macros, or VBA.
I have a cell that has a formula in it like PREM + PRDCMP + ACQ + DLR in a spreadsheet that looks like this:
PREM PRDCMP ACQ DLR VIP COMM TOTAL
2 1 3 4 6 5
PREM + PRDCMP + ACQ + DLR 2 1 3 4 10
where PREM + PRDCMP + ACQ are colored red and + DLR is colored blue. This formula is entered in that particular cell as a text string. I have a formula in the PREM column that says if the word PREM is located in the formula to the left (and it is), then put the value that is located right under the PREM column (2) in the blank cell next to the formula. So, in the above example, I have an IF statement that sees that PREM is in the formula to the left and so underneath the PREM column, next to the formula, it would place the number 2. Now, I need Excel to detect if any word in the formula text string is colored blue and if it is, to put a zero in that column. So, in the formula above, if + DLR is the only text that is blue, I want to have Excel add 2 + 1 + 3 only. I want to have Excel take any string out that might be colored blue, such as + DLR and return the value of zero for it. The purpose of the different colors in the formula is to distinguish which information is relevant and which information should be ignored. So, I'd like Excel to read the above formula and say put the PREM amount in the cell under PREM, put the PRDCMP amount in the cell underneath PRDCMP, put the ACQ amount in a cell under ACQ and put zero in the DLR column since it is colored blue. If any of them (PREM, PRDCMP, ACQ, DLR, VIP or COMM) are colored blue, I want Excel to put a zero in that column.
I am looking for the best way to change the font of cells in a Worksheet that contain a formula. I have used Go To-Special-Formulas but have one slight problem with this method. It highlights all cells that contain an "=" sign. Some cells may be linked (ie cell A2 may say =cell A1). How would I change the font only for cells with a Function such as Sum or Vlookup and not for cells that simply link to another cell?
View 7 Replies View Related1) Numerous cells in columns A and B on a w/s have a combination of two fonts each.
For example, cell A1 shows # 15 on the Formula Bar. The # sign is in "Wingdings 3" Font and 15 is in "Arial".
Cell A1 correctly displays |15 (i.e.; up arrow,space,15).
2) Other cells on the w/s reference those cells.
For example, cell C1:: =IF($J$5="Vortex",A1,B1)
When the above condition is true: Is there a way to return | 15 (i.e.; up arrow,space,15) in C1 , exactly as displayed in A1 ??
I have a rows of cells,each with over 30 columns.The writing is in 3 fonts,blue,black and red.
I want to award a score of 1 for a blue font,0 for afont cell and -1 for a red font and then get the total score for each row,to place in an additional column on the right.