help in excel condition based formatting

[huuhaaw]

HI SANDY HERE,

HI FRIENDS I NEED AN URGENT HELP AS I AM BEGINNER TO EXCEL AND LEARNING THINGS AS I CAME ACROSS, I HAVE A REQUIREMENT AND I HAVE MENTIONED A SAMPLE BELOW ANY HELP WOULD BE GREATLY APPRECIATED . WHOLE DATA VALUES ARE IN TEXT FORMAT.

THANKS.


Have 6 columns WITH DATA
SAMPLE :

	A	B		D	E	F
	POSTING KY	FLAG		Val posted 1	Val posted 2	Val posted 3
1	40	S			400.25	100.00
2	50	H		110.75	80.25
3	40	S		250.00		55.00
4	50	H			230.00	440.25
5	40	S		123.25	345.76
6	50	H		75.75		57.78

<tbody>
</tbody>

OUTPUT:

	A	B		D	E	F
	POSTING KY	FLAG		Val posted 1	Val posted 2	Val posted 3
1	40	S			400.25	100.00
2	50	H		-110.75	-80.25
3	40	S		250.00		55.00
4	50	H			-230.00	-440.25
5	40	S		123.25	345.76
6	50	H		-75.75		-57.78

<tbody>
</tbody>

FORMULA TRYING TO USE ON SINGLE CELL D3:

=IF(AND($A$1="50",$B$1="H"),-($D$3),($D$3))

SO AS COPY ACROSS WHOLE RANGE D6:F6 SO AS TO SHOW NEGATIVE FOR THE CONDITION WHEN TRUE AS MENTIONED IN FORMULA.

BUT ITS THROWING A CIRCULAR REFERENCE ERROR

PLS HELP..

 

[Peter_SSs]

Welcome to the MrExcel board!

You're new here so I'll draw your attention particularly to points 4 and 12 of the Posting Guidelines

As I understand it you are trying to conditionally format numbers that have 50 and "H" in columns A & B as negative numbers?
That is, the actual numbers will remain positive, but they will display as negatives.
If so, for that sample remove any existing conditional formatting then select D2:F7 and apply the Conditional Formatting formula shown.
The actual format is on the Number tab choose Custom then in the Type: box put -0.00 then OK etc

Excel Workbook
	A	B	C	D	E	F
1	POSTING KY	FLAG		Val posted 1	Val posted 2	Val posted 3
2	40	S			400.25	100
3	50	H		-110.75	-80.25
4	40	S		250		55
5	50	H			-230.00	-440.25
6	40	S		123.25	345.76
7	50	H		-75.75		-57.78
CF as Negative

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2	1. / Formula is =AND($A2=50,$B2="H")	Abc

If that is not what you want, post back with more details.

 

[huuhaaw]

excel condition based formatting

Hi Peter,
Thanks for the quick response, let me explain

1) the if condition is used on both column A and Column B and compare on each row of A and B. Based on condition on A and B columns, Column D,E and F are formatted.

example: if ($A2="50" and $B2="H") then condition is true , then check value in the corresponding rows in columns D,E,F(val posted 1,val posted 2, val posted)
then change the text in $D2,$E2,$F2 as negative if positive numbers

same formula is to be applied on all rows of columns D,E,F.

 

[Peter_SSs]

Re: excel condition based formatting

So you want to change the actual values from positive to negative when the conditions are met, not just change their formatting?

If so, you will have to state whether you want ..

a) A formula to create a new table of values elsewhere (say columns G:I for my screen shot),or

b) If you want the values replaced in their original cells, you would have to use a macro.

 

[huuhaaw]

Re: excel condition based formatting

Hi Peter,
Thanks for the response, you got it right I have to change the actual values from positive to negative when condition is met

so is it possible create the same copy of sheet1 in the new sheet2 with changed values instead of original values when condition is met .

regards,

 

[Peter_SSs]

Re: excel condition based formatting

.. I have to change the actual values from positive to negative when condition is met ..

But you didn't say formula or macro.
It could be done on another sheet by either formula or macro.

Here, I've done it on the same sheet just to confirm this is what you want.
If it is and you are looking for a formula approach you can probably adapt it yourself to using another sheet but post back if not or if you ant a macro.

Formula in H2 is copied across to I2 and down
Formula in K2 is copied across to M2 and down.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	POSTING KY	FLAG		Val posted 1	Val posted 2	Val posted 3		POSTING KY	FLAG		Val posted 1	Val posted 2	Val posted 3
2	40	S			400.25	100		40	S			400.25	100
3	50	H		110.75	80.25			50	H		-110.75	-80.25
4	40	S		250		55		40	S		250		55
5	50	H			230	440.25		50	H			-230	-440.25
6	40	S		123.25	345.76			40	S		123.25	345.76
7	50	H		75.75		57.78		50	H		-75.75		-57.78
Change to negative

 

[huuhaaw]

Re: excel condition based formatting

Hi Peter,
Thanks for the help I was looking for a macro in a new sheet.
Thanks

 

[Peter_SSs]

Re: excel condition based formatting

Thanks for the help I was looking for a macro in a new sheet.

Try ..

Sub FormatNegatives()
  Dim adrA As String, adrB As String, adrVal As String
  Dim lr As Long
  
  ActiveSheet.Copy After:=ActiveSheet
  With ActiveSheet
    lr = .Range("A" & .Rows.Count).End(xlUp).Row
    adrA = "$A$2:$A$" & lr
    adrB = "$B$2:$B$" & lr
    adrVal = "D2:F" & lr
    Range(adrVal) = Evaluate(Replace(Replace(Replace("if(len(#),if(@=50,if(%=""H"",-#,#),#),"""")", "#", adrVal), "@", adrA), "%", adrB))
  End With
End Sub

 

[huuhaaw]

Re: excel condition based formatting

Thanks so much for the help !!! much Appreciated